Anyone who does work with databases has probably had it drilled into their head that transactions can be used to increase performance in many cases. I’ve been doing a fair amount of work with sqlite recently, and got curious about just how much transactions could help in a particular case of mine. The answer: tremendously.
The basic idea of the test was to start from an empty database, create a table, and time how long it took to create some number of rows in that table. The table has two columns, an auto-incremented integer primary key and a second integer. In the autocommit tests, each row was created with its own implicit transaction (i.e. with autocommit). In the explicit transaction tests, a single transaction was created around the entire set of insertions and was committed at the end.
The two lines on this graph represent the amount of time (y) needed to create some number of rows (x) in an empty database.
It should be pretty apparent that autocommit absolutely kills performance in this case, so much so that I actually stopped testing it once I reached 1000 or so insertions…it was simply taking too long. But, where it was taking autocommit around 27 seconds to insert 1000 rows, the explicit transaction approach was handily inserting 10000 rows in 10ths of a second.
Now, I don’t want to read too much into this. This experiment was not remotely scientific and was mostly intended to put some kind of quantitative veneer on something I already more or less knew. But man…numbers like this really put the two approaches in stark contrast.