Benchmarking node:sqlite

[andykais]

I spent a little time today putting together some benchmarks for the different sqlite implementations that exist out there. I am in no way a sqlite expert so these benchmarks are just my best guess at good operations to score. What I found is pretty interesting though https://gist.github.com/andykais/fcecaf8f99260fa08e49e50a2eb46289. What I found was that in the basic case (single process sqlite, no WAL) @db/sqlite and node:sqlite are pretty close in performance on inserts and selects. However, when selecting unindexed data, the wasm sqlite library wins out by a lot. That may just be a quirk of the test. The important finding in this test though, was that when enabling PRAGMA journal_mode=WAL, the ffi sqlite implementation by @DjDeveloperr wins out by a large margin with inserts

group insert random data
NodeDatabase             60.2 µs        16,610 ( 29.9 µs … 136.3 ms)  45.1 µs  77.0 µs  91.4 µs
WASMDatabase             14.4 ms          69.5 ( 12.8 ms …  21.1 ms)  14.9 ms  21.1 ms  21.1 ms
FFIDatabase              18.2 µs        55,060 ( 12.3 µs …   2.3 ms)  16.2 µs  40.0 µs  51.0 µs
LibSqlDatabase           78.5 µs        12,740 ( 37.6 µs …  43.7 ms)  62.5 µs 164.6 µs 330.2 µs

summary
  FFIDatabase
     3.32x faster than NodeDatabase
     4.32x faster than LibSqlDatabase
   791.70x faster than WASMDatabase

I'm not exactly sure why this is the case, but its possible that there is some room for performance improvement on the native deno node:sqlite code

[andykais]

I should note that similar to other uses, I am really excited that we have a native sqlite implementation in deno now. This greatly reduces permissions needed to run sqlite apps (which was essentially -A before for sqlite ffi) as well as dependencies. I doubt that I will notice large performance reductions with this change, but I wanted to call these out anyways since I have used sqlite ffi in WAL mode for about a year now and I have gotten pretty spoiled by the speeds it gives me

[karimfromjordan]

We had a few discussions about performance on Discord and yeah... node:sqlite in Deno is roughly as fast as node:sqlite in Node. The original PR also had a benchmark for that: #27308 But that means it's also three times slower as something using FFI. Apparently Deno uses libsqlite3-sys but it's still unclear to my why it's so much slower because from what I understand libsqlite3-sys just provides Rust binding to SQLite's native C API,