On Jun 3, 2010, at 1:12 AM, Bart Lateur wrote: > On Wed, 26 May 2010 10:46:43 +1000, Owen wrote: > >> So I read the main database row by row, tested the validity of one >> column, and inserted the good rows into a new database. >> >> This process took 27 minutes on a fairly recent desktop computer. >> ( about 10000 rows a minute or 170 a second ) >> >> I thought the whole process would take a few seconds, so I wonder if 27 >> minutes is a reasonable time for this database creation. >
It's because SQLite is committing every row insert as its own transaction. Do a "begin transaction" at the start, then at the end a "commit transaction" and it'll fly with 50k rows/second. If you're ok with one big transaction where if it fails you have to redo it all, then just do the above. With 2.7 million rows that should take you barely a minute, so I'd say a single transaction for the whole procedure is the way to go. --- Henri Asseily henri.tel