Re: SQLite - Time to build a database

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