SQLite: what a difference transactions make

I received an email from something trying my simple Delphi wrapper for Sqlite. He wanted to add a million rows to a table of 10 columns of doubles, but was disappointed with the speed.

I tried with your SQL commands from the wrapper and just for 10000 elements it took me for ages…

I had a hunch that wrapping the inserts in a transaction might solve this one, and so it proved. The difference is staggering.

10,000 rows in 2 seconds, 1 million in under a minute.

Without the transaction it takes, well, forever, as the email says.

Worth noting if you use Sqlite; and in fact, many database engines behave like this. The reason I guess is that if you do not explicitly place a sequence of SQL statements within a transaction, then each statement is in effect its own transaction. That means the database engine has a lot of housekeeping to do in order to ensure that the changes were really written to disk, and in opening, writing and closing the journal file.

 

Technorati tags: , , ,

VN:F [1.9.18_1163]
Rate this post
please wait...
Rating: 0.0/10 (0 votes cast)

Related posts:

  1. C#-SQLite now published
  2. SQLite will be everywhere
  3. SQLite wrapper for Delphi
  4. Extend SQLite with Delphi functions
  5. SQLite with Delphi