perf: avoid repeated full query-result copies #310

Summary

The current query-result bridge can copy/materialize the full result set repeatedly. In particular, iterating through rows.item(i) can trigger a full native result copy and bridge conversion for every row, making the access pattern effectively O(rows²).

This is a concrete, current-main issue separate from the broad historical performance discussion in #62 and #30. It is also distinct from #185 (a user-observed memory spike without a verified cause) and the merged memory-accounting work in #261/#287.

Verified by source audit on main at ad8b835ba0f44a207649ecc2953820d39e4e8639 (v9.7.0).

Code evidence

Native getter returns the entire result vector by value

The backing result type is a vector of unordered-map rows:

• types.hpp#L13-L17

The hybrid implementation stores the result set in _results, but the getter returns SQLiteQueryResults by value:

• HybridNitroSQLiteQueryResult.cpp#L74-L76
• HybridNitroSQLiteQueryResult.hpp#L18-L29

The generated spec also requires a by-value vector return and registers it as a hybrid getter:

• HybridNitroSQLiteQueryResultSpec.hpp#L53-L58
• HybridNitroSQLiteQueryResultSpec.cpp#L16-L20

Copying the vector copies each row map; string values are copied and ArrayBuffer shared pointers are retained. The original result is materialized row-by-row here:

• operations.cpp#L202-L245

The JS compatibility wrapper reads the getter repeatedly

buildJSQueryResult currently reads result.results separately for _array, length, and item:

• execute.ts#L36-L48

The relevant behavior is:

resultWithRows.rows = {
  _array: result.results,
  length: result.results.length,
  item: (idx) => result.results[idx],
}

Because results is a hybrid getter, item(i) evaluates that getter on every call. A loop over N rows therefore requests N full result vectors, rather than indexing one already-materialized array. The public rows contract is documented here:

• types.ts#L39-L59

Smallest reproducer

const db = open({ name: 'result-copy.sqlite' })

db.execute(`
  CREATE TABLE rows (
    id INTEGER,
    name TEXT,
    mime_type TEXT,
    size INTEGER,
    created_at TEXT,
    payload TEXT
  )
`)

// Seed N rows, then:
const result = db.execute('SELECT * FROM rows ORDER BY id')
const rows = result.rows

for (let index = 0; index < rows.length; index++) {
  void rows.item(index)
}

The loop should index one JS result array. On current main, each rows.item(index) call reaches result.results[index], which invokes the by-value hybrid getter again.

Why this matters

• Large list/search queries pay repeated native allocations, row-map copies, and JS bridge conversion.
• A consumer using the established rows.item(i) compatibility API can accidentally turn a linear result traversal into quadratic work.
• Peak memory can include multiple temporary full result sets while the previous temporary is still referenced by the getter conversion.
• This is especially relevant for mobile apps that fetch 60 visible rows, 1,000 search rows, or 10,000 offline records.

Benchmark plan

Compare main with a candidate implementation on a representative six-column table such as the reproducer above.

Run both execute and executeAsync, after one warm-up query, with 20 measured iterations and report median/p95:

1. 60 rows: visible-page/UI workload.
2. 1,000 rows: normal search/export workload.
3. 10,000 rows: large offline query workload.

Measure these consumers separately:

• result.rows._array access.
• result.rows.length access.
• A complete for (i = 0; i < rows.length; i++) rows.item(i) traversal.

Run on at least one iOS and one Android device/emulator in Release and Debug/Hermes where practical. Record wall time, JS heap/GC pressure, and native external memory. The benchmark should show that the complete item traversal is linear in N after the fix; current behavior is expected to show super-linear growth because each item access rematerializes the complete result.

Proposed direction

At minimum, cache the hybrid property once in buildJSQueryResult:

const results = result.results as Row[]

resultWithRows.rows = {
  _array: results,
  length: results.length,
  item: (idx) => results[idx],
}

This is backward-compatible and removes the repeated getter calls. A follow-up native/Nitro design can determine whether the first by-value vector copy can be avoided or replaced with a shared/hosted result view without exposing mutable native state.

Acceptance criteria

• results, rows._array, rows.length, and rows.item(i) preserve their current public shapes, ordering, value types, NULL handling, BLOB behavior, and out-of-range behavior.
• Sync and async execution have the same result semantics.
• The JS wrapper reads the native results getter at most once per query result.
• A full rows.item(i) traversal does not rematerialize the full native result for each row.
• The 60/1,000/10,000-row benchmark demonstrates linear scaling for the fixed traversal, with no additional full-result peak copy beyond the intended one-time conversion.
• Harness coverage exercises both rows._array and rows.item(i) for all three sizes, including a BLOB/NULL regression case.
• Any deeper Nitro change to eliminate the first vector copy is separately benchmarked and does not expose mutable native state or break hybrid-object lifetime.

Related

• General development suggestions #62 — broad development/performance suggestions including extra copying.
• Make quick-sqlite 2x quicker? #30 — historical performance experiment for the predecessor API.
• High memory usage & unexpected logging just by importing NitroSQLite.executeAsync #185 — high-memory report without a verified root cause.
• feat: Implement Nitro's getExternalMemorySize #261 and fix: correct query result memory estimate #287 — external-memory accounting work; they do not remove repeated result getter copies.
• fix: Keep hybrid object data #255 — prior hybrid-object data/lifetime work; this report concerns repeated result property materialization on current monorepo main.