On Fri, Feb 20, 2009 at 11:56:52AM -0500, Boucher, Michael scratched on the wall: > Hi there, > > I need to migrate data from a different database into a SQLite database. > What I've done is written a simple C++ app which opens both databases, > does a select from one and an insert into the other. > > I'm wondering if there is a way to delay indexing on the insert. I'm > going to be inserting between 1 and 4 million records in a couple of the > tables. I want to just get the data into the table, and then let the > database handle the indexes after all the inserts are done.
The indexes will not be updated during a transaction, so you can wrap anywhere from 100 to 10000 or so inserts in a transaction. When you commit the transaction the index will be updated, but it will be a bit quicker to do it in chunks. Also, make sure you bump up the page cache size significantly. Depending on how much RAM you have 200,000 or more is not unreasonable. That will help with re-index speed. It is possible you could just do this from sqlite3 by attaching both DBs and running something like: INSERT INTO newdb.table ( <columns> ) SELECT <columns> FROM olddb.table I'm not sure what the performance will be with 4 million rows, but that should wrap itself in a transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
