SQLite Write-Ahead Logging

To make SQLite even faster you can enable a feature called Write-Ahead Logging (aka WAL).

Normally when you update your SQLite Database within a transaction, the original data is copied to a separate 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 SQLite Pragma command:

PRAGMA journal_mode=WAL;

In Xojo code , you send the Pragma command using SQLExecute:

DB.SQLExecute("PRAGMA journal_mode=WAL;")

Or more simply you can set the SQLiteDatabase.MultiUser property to True:

DB.MultiUser = 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 SQLite 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;

In Xojo code, you can send this Pragma command to the DB using the SQLExecute command:

DB.SQLExecute("PRAGMA schema.wal_checkpoint;")

There are downsides to using WAL. Performance for reads can suffer when there is 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, WAL is worth using. Test it with your apps to see if they get a benefit.