Everyone loves SQLite. It is everywhere, it is fast and it is easy to use. Want to know how to make it even faster?
Here’s the secret: enable Write-Ahead Logging (aka WAL).
Normally when you update your SQLite Database within a transaction, the original data is copied to a rollback file. The new data is written directly to the DB file. This results in two disk writes for every DB change. When you COMMIT, the rollback file is removed. Should you ROLLBACK, then the data is restored from the rollback file.
WAL can be faster because it reverses this. With WAL each change to the SQLite Database is written to a separate “write-ahead logging file” (which typically ends in “-wal”). This results in just a single disk write.
Additionally, because of the separate files, an app with multiple threads is able to read from the DB while it is being written to. And vice versa. This is an especially nice benefit for web apps that need a DB, but do not require a database server.
To enable WAL, you can use the Pragma:
PRAGMA journal_mode=WAL;
Which you can send using SQLExecute:
DB.SQLExecute("PRAGMA journal_mode=WAL;")
Or you can set the SQLiteDatabase.MultiUser property to True:
DB.MultUser = True
The data in the separate WAL file will at some point have to be transferred back to the original database. This is called a “checkpoint”. You can do these manually or let SQLite handle them automatically, which according to the docs happens when the WAL file reaches a threshold of 1000 pages.
You can manually initiate a checkpoint using the wal_checkpoint Pragma command:
PRAGMA schema.wal_checkpoint;
You can send this command to the DB using the SQLExecute command:
DB.SQLExecute("PRAGMA schema.wal_checkpoint;")
Now to be fair, there are downsides to using WAL. Performance for reads can suffer with a large WAL file because data has to be searched in two places. The official SQLite docs on WAL also list some of the other downsides.
But for most types of usage, I think WAL is worth trying. Enable it for your apps to see for yourself!
