> -----Original Message----- > From: Andrew Cheyne [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 18, 2006 8:08 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] sqlite performance questions.
[snip] > I have then been writing some sample C programs making use > of the C API, > but have been very disappointed in the performance of > Œinsert¹ing into the > database. For example, timing the performance of executing an insert > statement into this table only gives me an insertion rate of > 6 rows per > second (³insert into Node (url, filename) values (Œfoo¹,¹bar¹);²). 3 words -- Transaction Transaction Transaction! Start a transaction before you start bulk inserting, and commit it afterwards. You are being bitten by SQLite's ACID compliance. Any statement not wrapped in a transaction is automatically placed inside its own transaction. 6 rows per second is the fastest rate at which a transaction can be spun up, 1 insert performed, the buffers flushed to the physical disk, and the transaction torn down. By starting the transaction beforehand, and committing it when you're done, you save the worst parts of the task for the beginning and end, and only perform them once. Robert ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
