Some SQLite 3.7 Benchmarks

Since I wrote the benchmarks for insertions in my last post, SQLite 3.7 has been released. I figured it’d be interesting to see if 3.7 changed the situation at all.

Prepared Statements

The specific versions compared here are 3.6.23.1 and 3.7.3.  I ran the prepared statements benchmark as is without changing any source code.  Both are using a rollback journal in this case.

Prepared Statements Runtime in Seconds

Prepared Statements Inserts Per Second

As you can see, the new version of SQLite definitely provides better performance.  There is a speedup of about 3 seconds.

Journal Mode Comparison

One of the main features SQLite 3.7 added was Write Ahead Logging (WAL).  The main advantage of write-ahead logging is it allows more concurrent access to the database than a rollback journal.  These benchmarks don’t show the true potential of write-ahead logging.  The benchmarks are single threaded, and they insert a large amount of data in one transaction.  It’s listed as a disadvantage that large transactions can be slow with write-ahead logging, even having the potential to return an error.  I wanted to evaluate write-ahead logging as a drop in replacement.

I ran the prepared statements benchmark with the default, memory, and wal settings.  I also ran each setting with and without synchronous on.  The synchronous setting controls how often sqlite waits for data to be physically written to the hard disk.  The default setting is full, which is the safest because it waits for data to be written to the hard disk most frequently.  This is compared to synchronous being off, which lets the operating system decide when information should be written to the hard disk.  In this case if there is a software crash, it’s more likely the database could become corrupt.

Journal Mode Runtime Comparison

Journal Mode Inserts Per Second

Up until about 100,000 insertions all the journal modes and synchronous settings are about even.  After 100,000, the insertion benchmarks with synchronous off are faster than their default synchronous counterparts.  Journal mode set to memory and synchronous off offered the best performance for this benchmark.