Performance Comparison of SQLite/OPFS versus WebSQL

The Performance Benchmark Program: `speedtest1`

The SQLite project has been using a performance measurement tool called "speedtest1" for many years. Speedtest1 runs a variety of SQL statements in an attempt to simulate a "typical" SQLite workload. Speedtest1 is the standard performance measurement tool for SQLite. It is used to generate graphs such the one near the top of https://sqlite.org/cpu.html.

The default mode of operation for speedtest1 is for speedtest1 to invoke SQLite API routines directly. However, a run-time option can cause speedtest1 to emit pure SQL statements which can then be piped into a separate SQL execution engine to perform the tests. Speedtest1 also has a run-time option to control the "size" of the test, which changes both the size of the database and the number of queries executed against the database.

Two Ways Of Testing

Direct execution means that the "speedtest1" program is run directly. Speedtest1 makes SQLite API calls to run the SQL statement.
Script execution means that "speedtest1" is run with the --script option to generate a file that contains the (thousands) of SQL statements that would be run for a performance test, then that file of SQL statements is piped into the execution engine in order to run the test.

Due to design limitations, performance tests against WebSQL can only be run in script mode. In other words, to test the performance of WebSQL, we have to feed it a big script with thousands of SQL statements. The other variants of SQLite can all be run using either modality.

Variants Of SQLite Under Test

These are the different builds of SQLite that we compare:

C-native speedtest1. This means running the "speedtest1" program on Linux in a shell.
WebSQL. This is the deprecated SQLite interface that is built into Chrome. It does not use WASM. It is coded directly in C/C++. WebSQL only runs in the main thread.
WASM in-memory. This is SQLite compiled using Emscripten and using the in-memory file system provided by Emscripten. No persistent I/O occurs.
WASMFS. This is SQLite compiled using Emscripten's WASMFS filesystem that does support persistence. This variant currently only works in the main thread.
sqlite3_vfs. This is SQLite compiled to WASM and using OPFS to implement persistence without any help from Emscripten. This variantly currently only functions in worker threads.
local/session storage. This is SQLite compiled to WASM and using localStorage or sessionStorage for persistence. This variant only functions in the main thread. Only smaller performance tests can be run on this variant due to browser-imposed limitations on the amount of local/session storage.

Script-Mode Performance Comparisons

The following table shows approximate run-times (in milliseconds) for speedtest1 runs of various sizes in script mode.

SQL Statement Count	WASM in-memory	WebSQL	WASMFS
14200	2200	2230	2240
71000	3050	4250	3400
177500	6200	6000	6900
355000	16000	10900	16350
710000	52000	40000	57200

Direct-Mode Performance Comparisons

The following table shows approximate run-times (in milliseconds) for running speedtest1 directly (compiled to WASM where appropriate).