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.