This issue has been raised before, but I haven't found a solution in list archives. If I missed it, please point me in the right direction.
I have a database with a single table that has only one text column. This table has ~10M rows at the moment but it's growing by a 300-400K rows per week. On average there's 20 characters in each row. File size (without index) is ~285MB. I need to do two things with this database: - insert new rows while maintaining uniqueness - do a fast lookup to determine if given string exists in db UPDATE and DELETE are never run on this db. After some experimentation I found that it's actually faster, at least for INSERTs, to keep db on disk without index and load it to memory db on request and create unique index there. After performing all INSERTs/SELECTs memory db is dumped back to an on-disk db without index. Loading db from disk to memory by attaching disk db to :memory: and doing CREATE TABLE ... SELECT... takes around 2 minutes. Creating unique index in memory db takes some 45 minutes. After db is loaded into memory, I'll either be inserting 300-400k rows and dumping the db back to disk or I'll be performing some batch lookups and closing the memory db afterwards. Even though this is a once a week thing, I still need to speed it up. I see 2 possible solutions: 1. Find some way to move file db to memory db and back at near disk speed (avoid creating index every time) 2. Do it outside of SQLite. I prefer first option since I'm using that db from C and Python and having off the shelf solution for Python is a great time saver. And it will probably come in handy in the future since I might need to integrate this with some other SQLite db. Back in June DRH posted some code to copy file db to memory db. http://www.mail-archive.com/[email protected]/msg15905.html This seems to be a good part of the solution to my problem, except I can't get it to work or even compile. Did anyone ever managed to use sqlite3_raw_write() successfully? Though I'd still need to move that memory db back to file somehow. Second option is something I've been trying to avoid for some time, but as db grows things only get worse with current setup so I have to do something. Could anyone recommend some off the shelf, or close to it, solution for this? Any help/pointers are very much appreciated. Thanks for reading this far. -- Nemanja Corlija <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
