Apr 6, 2026 · Accepted

Experiment 009: Batch FFI with resqlite_step_row

Date: 2026-04-06

Status: Accepted

Commit:4c18bb4

Problem

The select() hot loop makes ~16 FFI calls per row: 1 sqlite3_step + 6 sqlite3_column_type + 6 sqlite3_column_xxx (value read) + ~3 sqlite3_column_bytes (text lengths). For 5,000 rows, that's ~80,000 FFI calls. Each FFI boundary crossing costs ~10-20ns.

A micro-benchmark confirmed: the C bulk reader (one FFI call for everything) completed the same query in 0.68ms, while the full select() pipeline took 2.42ms. The overhead (1.74ms) was 72% of the total, with FFI calls accounting for ~80% of that overhead.

Hypothesis

A C function that steps one row and fills a pre-allocated native struct array with all column types and values in one call would reduce FFI crossings from ~16/row to 1/row. Dart reads values from native memory via ByteData (no FFI call). Estimated savings: ~1ms at 5,000 rows.

What We Built

 typedef struct { int type; long long int_val; double double_val; const char* text_ptr; int text_len; const void* blob_ptr; } resqlite_cell; int resqlite_step_row(sqlite3_stmt* stmt, int col_count, resqlite_cell* cells); 

Dart pre-allocates one resqlite_cell array (native memory, reused across rows). Each resqlite_step_row call fills the array. Dart reads type tags and numeric values directly from ByteData (getInt32/getInt64/getFloat64). Text pointers and lengths are read as int64 addresses, then used with Pointer.fromAddress + utf8.decode.

Results

Maps wall time

Concurrent reads (8 parallel, 1,000 rows)

Parameterized queries (100 queries × ~500 rows)

Schema shapes — wide tables benefit most

Wide tables see the biggest improvement (more columns = more FFI calls saved per row). Text-heavy tables see minimal improvement (utf8.decode dominates regardless of FFI overhead).

Why Accepted

Consistent 9-21% improvement across all benchmarks. The improvement compounds with the connection pool (concurrent reads improved from 1.29ms to 1.07ms) and statement cache (parameterized queries dropped from 23ms to 19.89ms). resqlite now holds the top position on every benchmark we measure.

Final standings after all optimizations