Benchmarks

OneLite's own benchmark suite lives in bench/OneLite.Bench. It compares OneLite with the reference SQLite library at two levels and measures nothing else. The tables below are the first published baseline (commit 7e028d2). Speed is a target, not a release gate: the reference ratios are information, and the first baseline tables below predate the tuning described under "Tuning: prepare and point lookups".

Method

Levels

• Engine: OneLite's Statement.Step (sync driver) against the native sqlite3_step, called through a raw LibraryImport of libe_sqlite3 3.53.4. The P/Invoke code exists only in the bench project.
• App: RecursiveAI.OneLite.Data against Microsoft.Data.Sqlite 10.0.14 on SQLitePCLRaw.bundle_e_sqlite3 3.0.5 (SQLite 3.53.4). Both go through one shared driver, so the code above the ADO.NET API is identical.

Settings, identical on both sides. Page size 4096, cache_size = -2000 (2 MB), the same file system (a scratch directory on local disk), the same data generator and seed, the same SQL. journal_mode and synchronous are set per row by PRAGMA on both sides (default WAL/NORMAL; insert_autocommit uses delete/FULL; wal_commit_full uses WAL/FULL). The base table has 100,000 rows. Each side computes a checksum of what the queries returned and the report shows "Results agree".

Measurement. Every row × level × side runs in its own child process, so peak memory is clean (it includes the runtime baseline of roughly 35–50 MB). Each runs one warm-up and 3 timed repetitions; the report gives the median. The allocation column is GC.GetAllocatedBytesForCurrentThread per operation. There is no BenchmarkDotNet; the harness is a plain stopwatch loop, because 60+ process-isolated measurements would not fit the time boxes.

Runs. One run per machine, in two groups (A: write, prepare, open and cold-start rows; B: read and query rows), each run alone on the machine by a queue that does not start anything else beside a timing job. The two groups are merged; the geometric mean covers every row except open_time, which is reported only.

Reproduce with bench/run.sh --rows <a,b|all> --levels engine,app --reps 3 --out <prefix> (--vec points at the sqlite-vec loadable for the vec0_knn reference; --aot at the published bench/OneLite.Bench.Cold for the NativeAOT cold-start row). OneLite.Bench merge combines filtered runs.

Rows. insert_tx_10k and insert_tx_100k (bulk insert in one transaction); insert_autocommit (200 single-row commits); prepare_cold (first prepare of new statement texts); wal_commit_normal and wal_commit_full (1,000 commits); lookup_rowid and lookup_index (200,000 point lookups); scan_filter (unindexed scan with a filter); range_scan; join (two tables); group_by; order_by_sort (sort larger than the cache); fts5_top10 (FTS5 MATCH, top 10 by bm25); vec0_knn (vec0 k-NN, against sqlite-vec 0.1.9's prebuilt extension); prepare_cost; open_time (open, one query, close). Cold start (process start to the first answer, JIT and NativeAOT) is separate.

Read these first

• fsync against fdatasync. On Linux the pinned libe_sqlite3 issues fsync for each commit sync and OneLite issues fdatasync. The number of syncs is the same (about 2,012 for 2 × 1,000 commits in strace), but fdatasync skips the file-metadata flush, so wal_commit_full (0.28× on x64) and insert_autocommit (0.85×) are faster than native for a reason that is not engine speed. It is a property of the reference build, not a durability gap by count. On macOS the two sides are close (1.9× and 1.1×).
• Concurrent writers. The BEGIN CONCURRENT throughput row is not part of this baseline. It is pending until the feature's measurements are ready; there is no number for it here.
• Short rows include JIT. insert_tx_10k runs about 80 ms, and the warm-up is a single run, so tier-0 code is part of the timing. insert_tx_100k shows the steadier ratio (3.9× against 18.7× on x64).
• Allocation. The cached hot path allocates nothing per row stepped (scan_filter 0.2 B/op, join 0.4 B/op) and, since the tuning below, nothing per statement execution of a point lookup (bind, step, reset: 0 B at engine level). Preparing a statement that was prepared before on the connection allocates about 140 B; a first prepare about 23 KB. fts5_top10 allocates 5.7 MB per query.
• Mac load. The Mac was not idle by itself: its 1-minute load average sat near 2 before, during and after both groups, with no other queue job running. Treat the arm64 numbers as slightly less quiet than the x64 ones.

x64: AMD Ryzen 9 7950X3D, 32 cores, Linux Mint 22.3

.NET 10.0.13 (SDK 10.0.402), SQLite 3.53.4, Microsoft.Data.Sqlite 10.0.14. Load average (1 min): group A 0.76 before, 1.83 highest, 1.77 after; group B 1.25 before, 1.28 highest, 1.24 after. Group B was run twice. The first run started at a load of 4.54 and could not be shown to have been alone, so it was discarded and re-run; the re-run agrees with it to within a few percent on every row.

Engine level

Geometric mean, 15 rows: 2.56× (target ≤1.15×; per row ≤1.5×).

App level

Geometric mean, 15 rows: 2.47× (target ≤1.0×; per row ≤1.25×).

Cold start (report only)

arm64: Apple M2, 8 cores, macOS 27.0.1

.NET 10.0.11, SQLite 3.53.4, Microsoft.Data.Sqlite 10.0.14. Load average (1 min): group A 2.05 before, 2.05 highest, 1.97 after; group B 2.04 before, 2.21 highest, 2.16 after (see the note above).

Engine level

Geometric mean, 15 rows: 3.62×.

App level

Geometric mean, 15 rows: 3.47×.

Cold start (report only)

The five worst engine rows, with the suspected cause

No fixes have been made; each is a candidate for its own tuning unit. Ratios are x64 / arm64.

1. prepare_cost (19.5× / 17.5×): parse, plan and code generation allocate about 23 KB per statement. Fixed for repeated prepares by statement reuse, see "Tuning: prepare and point lookups"; a first prepare of a text still costs this much.
2. insert_tx_10k (18.7× / 15.6×): the row is short (about 80 ms) so JIT and tier-0 code dominate; the same insert path is 3.9× / 5.1× at 100,000 rows, where the remaining gap is per-row allocation (435 B/op) and the record build.
3. fts5_top10 (10.9× / 15.1×): 5.7 MB allocated per query, which points at the FTS5 segment and doclist readers copying into fresh buffers instead of reading spans in place.
4. group_by (5.7× / 5.2×): the aggregation and the grouping sorter or hash path (122 B per row), not the scan.
5. range_scan (4.5× / 6.5×): each execution re-seeked and allocated about 600 B, and every index entry was followed to the table row. Both fixed, see "Tuning: prepare and point lookups"; the unindexed scan_filter (4.4× / 6.0×) points at the dispatch loop's per-row cost rather than I/O.

Also visible: open_time is 36× on Linux and about 1.2× on macOS, so a Linux-only open or close cost (locks, -shm, recovery on open or the closing checkpoint) is suspected.

Tuning: prepare and point lookups

Three changes, measured on the same machine in one run, before and after: (1) a finalized statement leaves its program, machine and cursors with the connection and the next prepare of the same text takes them (schema, foreign_keys and encoding unchanged; at most 32 texts); (2) a read transaction in WAL mode starts and ends without allocating and, while the wal-index snapshot is unchanged, skips re-reading the database size and page 1; (3) a query on one rowid table that an index answers completely reads the index alone. x64 only (AMD Ryzen 9 7950X3D, 32 cores, Linux Mint 22.3), .NET 10.0.13, SQLite 3.53.4, Microsoft.Data.Sqlite 10.0.14, 3 repetitions after a warm-up, the run alone on the machine (load average 2.2 before the first half and 1.6 before the second, CPU idle when it started). The ratios are against native SQLite; B/op is OneLite's allocation per operation.

prepare_cost prepares the same three texts 1,000 times, so after the first round every prepare is a reuse; native SQLite compiles each time. Read its ratio as the cost of preparing a text again, not of compiling.

Engine level

App level (RecursiveAI.OneLite.Data against Microsoft.Data.Sqlite)

Cold prepare (first prepare of a new statement text)

prepare_cold prepares three statements of the prepare_cost shapes 1,000 times, each round with a new literal, so no text has been prepared before and nothing is reused. Read it next to prepare_cost (reuse): reuse is 0.65 ms, a first prepare is about 160 ms, because the reuse cache only helps repeated text. Indicative (a shared heavy queue slot, not a bench slot, 3 repetitions, one x64 Linux machine, 4 cores reserved, load average about 6.5 before and 7.5 after; native SQLite 3.53.4 and Microsoft.Data.Sqlite as in the other rows):

What is left. lookup_index and range_scan still evaluate the whole WHERE clause on every index entry, as the engine always does (a plan can change speed, never the answer). Every read transaction costs two fcntl calls (taking and releasing the wal-index read mark), as it does in the native library; on this machine starting and ending a read transaction around a trivial query takes about 1.8 µs, most of it those calls.

Where OneLite is faster

join (0.34× / 0.49×; the plan differs and the checksums agree), vec0_knn (0.46× / 0.79× at engine level, from the SIMD kernels), and on x64 wal_commit_full and insert_autocommit (see the fsync note above).