SQLite benchmark is flawed or at least misleading

[Prinzhorn]

What is the type of issue?

Documentation is incorrect

What is the issue?

This is just a quick drive-by. I had never heard of Bun until yesterday and the first thing I saw was the claim that the SQLite module is "roughly 3-6x faster than better-sqlite3" (I saw a YouTube video on Bun and this claim was blindly copied into the video and I immediately stopped watching the video at this graph). Given that 99.9% of the time should be spent in the exact same SQLite C code this immediately sounded like a fluke. And the past has shown again and again that benchmarks are usually broken.

Looking at the benchmark we can see that all queries are literally doing a SELECT *. So right from the start this mostly tests IO speed because we're just reading all the data. And since this is done by SQLite the question is why would Bun be faster? I have no idea how Bun works but my interpretation is that you are essentially benchmarking how fast Bun (or JavaScriptCore) can turn the SQLite rows into JavaScript arrays / objects. Which is not directly related to SQLite (but of course great if Bun is faster there). And no real world application will do SELECT * on 600k rows (OrderDetail). My conclusion is that this benchmark is broken, as most are.

If we add an actual benchmark that tests SQLite speed with a real query (where the time is spend doing SQLite things and not IO or converting things into the JavaScript world), we find that better-sqlite3 is faster (346.42 ms/iter vs 296.85 ms/iter). I copied this query from https://github.com/drizzle-team/drizzle-northwind-benchmarks/blob/f54e47dfea555c690771fde585fe4a2585f7b53e/src/better-sqlite3/benchmark.ts, maybe you can take inspiration from the variety of test cases in their benchmark (funny enough, theirs was broken as well, claiming Drizzle was faster while using better-sqlite3 as a driver because GC was not taken into consideration).

{
  const sql = db.prepare(`
    SELECT o."Id", o."ShippedDate", o."ShipName", o."ShipCity", o."ShipCountry",
          COUNT(od."ProductId") AS products_count,
          SUM(od."Quantity") AS quantity_sum,
          SUM(od."Quantity" * p."UnitPrice") AS total_price
    FROM "Order" AS o
    LEFT JOIN "OrderDetail" AS od ON od."OrderId" = o."Id"
    LEFT JOIN "Product" AS p ON p."Id" = od."ProductId"
    GROUP BY o."Id"
    ORDER BY o."Id" ASC;
  `);

  bench("Complex SELECT", () => {
    sql.all();
  });
}

 $ bun bun.js
[0.01ms] ".env"
cpu: 11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz
runtime: bun 1.0.0 (x64-linux)

benchmark                        time (avg)             (min … max)       p75       p99      p995
------------------------------------------------------------------- -----------------------------
SELECT * FROM "Order"          17.6 ms/iter   (16.58 ms … 20.44 ms)  18.12 ms  20.44 ms  20.44 ms
SELECT * FROM "Product"       44.02 µs/iter   (36.8 µs … 892.93 µs)  42.99 µs 112.61 µs  117.7 µs
SELECT * FROM "OrderDetail"  249.11 ms/iter (193.48 ms … 326.27 ms) 306.84 ms 326.27 ms 326.27 ms
Complex SELECT               346.42 ms/iter (345.39 ms … 349.49 ms) 346.41 ms 349.49 ms 349.49 ms


 $ node node.mjs
cpu: 11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz
runtime: node v20.6.1 (x64-linux)

benchmark                        time (avg)             (min … max)       p75       p99      p995
------------------------------------------------------------------- -----------------------------
SELECT * FROM "Order"          53.1 ms/iter   (52.37 ms … 55.23 ms)  53.34 ms  55.23 ms  55.23 ms
SELECT * FROM "Product"      153.33 µs/iter (144.64 µs … 606.34 µs) 152.78 µs 173.57 µs 181.87 µs
SELECT * FROM "OrderDetail"     1.17 s/iter    (999.27 ms … 1.74 s)     1.2 s    1.74 s    1.74 s
Complex SELECT"              296.85 ms/iter (293.28 ms … 299.53 ms)  298.4 ms 299.53 ms 299.53 ms

By no means do I want to shit on Bun here, but this is not the first time that I've seen outrages claims (3x - 6x is massive) based on broken benchmarks. These claims should've raised eyebrows the second the benchmark was run for the very first time by whoever wrote it. I'm also not saying that Bun is always slower, yes it can be (much) faster under very specific circumstances. But between testing IO speed, actual SQLite performance and Bun internal specifics the benchmarks are heavily skewed towards Bun (or JavaScriptCore), when in reality (for actual apps) the difference might be small or even non-existent.

Maybe someone can migrate all benchmarks from the Drizzle benchmark over, I'd be curious to see the results. I'm in chronic pain and this post was my quota for the week I guess.

Where did you find it?

No response

[Prinzhorn]

From what I can tell the 3-6x might correlate 1:1 to the FFI performance? Which means my conclusion would be right that the performance gains only come into play when there's a lot of conversion going on between C and JavaScript. But with actual SQL queries that do more than SELECT * this becomes more and more negligible because SQLite needs to do a lot more work on the C end of things.

Edit: From what I understand it would only be a matter of time for Node.js to catch up then? nodejs/node#46233

[DoctorGester]

I don't get the confusion here? It's obviously the exact same SQLite in both Bun and node so it wouldn't it be obvious for anyone that the benchmark displays the overall improvement in JS-SQLite interop?

Just like better-sqlite3 has benchmarks against sqlite3 package which I don't think confuse anyone.

[Prinzhorn]

Fair enough, I was probably the only one confused given that I've never heard of Bun or FFI before. I'd like to see a line added to the SQLite docs though explaining where the difference comes from.

The docs also say:

Each driver was benchmarked against the Northwind Traders dataset.

Which might confuse readers that the performance difference has been measured for real-world use, which is not the case. If this benchmark is only to showcase the interop performance then the following with an in-memory db is all you need:

SELECT 42, 3.14, 'foo', NULL, X'01'

$ node --expose-gc node.mjs 
cpu: 11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz
runtime: node v20.6.1 (x64-linux)

benchmark                                time (avg)             (min … max)       p75       p99      p995
--------------------------------------------------------------------------- -----------------------------
SELECT 42, 3.14, 'foo', NULL, X'01'     1.3 ms/iter      (1.2 ms … 1.52 ms)   1.38 ms   1.49 ms   1.51 ms


 $ bun bun.js 
[0.01ms] ".env"
cpu: 11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz
runtime: bun 1.0.0 (x64-linux)

benchmark                                time (avg)             (min … max)       p75       p99      p995
--------------------------------------------------------------------------- -----------------------------
SELECT 42, 3.14, 'foo', NULL, X'01'  595.52 µs/iter   (470.21 µs … 1.13 ms) 627.27 µs 914.09 µs 966.86 µs

Just like better-sqlite3 has benchmarks against sqlite3 package which I don't think confuse anyone.

I've been triaging issues in the better-sqlite3 repo for multiple years now and have seen every single issue that was opened. Many users don't understand what a native module is, why better-sqlite3 needs a compile step, why it can't be copied between different architectures or what the difference between sync and async is (randomly putting await in front of all better-sqlite3 calls). Some don't know that SQLite does not run as a separate "database" and is embedded into better-sqlite3. Because most don't know that SQLite is essentially a single C file that everyone can freely use. So I definitely strongly disagree with "it's obvious" and "nobody is confused".

[davidboschwitz]

I was wondering if it could be because they do not enable as much of sqlite3.c's functionality as better-sqlite3. I don't think this is intentional but I do wonder if it does impact the benchmark. I've cut #4772 about some missing math functions that I use

[houd1ni]

@Prinzhorn No way, buddy, - pure marketing here, hence no replies here 😆 . If it's an issue, it's maybe worth it to have some note on better-sqlite3 presentation side (that bun's impl could be as much as 0.001% better performance against that set due to better io part).