Skip to content

Latest commit

 

History

History
269 lines (226 loc) · 9.58 KB

README.md

File metadata and controls

269 lines (226 loc) · 9.58 KB
Raw

Lighter Performance Test Suite

This repository contains code to test the Lighter performance. It uses the (populated) Northwind SQLite database as the base set. It doesn't have the goal to be scientifically proper.

Results

It evaluates the load performance on queries against the "Orders" table, which has 16K records. The test loads all 16K records on each iteration.

Lighter is expected to perform excellent, as it directly/statically binds SQLite prepared statements for the generated structures.

M1 Mini with 10 rampup iterations and 500 test iterations.

Orders.fetchAll    setup    rampup   duration
  Enlighter SQLite 0        0,135s     6,629s   ~20% faster than Lighter (75/s)
  Lighter          0        0,162s     7,927s   Baseline                 (63/s)
  GRDB             -        -        ~12s       Handwritten Mapping
  SQLite.swift     0        0,613s    30,643s   Handwritten Mapping (>3× slower) (16/s)
  GRDB             0,001    0,995s    49,404s   Codable (>6× slower)     (10/s)
  SQLite.swift     0,001    3,109s   153,172s   Codable (>19× slower)    (3/s)

Essentially the specific testcase with no handcrafting involved needs 30 secs on GRDB, which is state of the art, 2.5 minutes w/ SQLite.swift and not about 8 secs with the Lighter API.

As a chart:

┌─────────────────────────────────────────────────────────────────────────────┐
├─┐                                                                           │
│S│                Enlighter generated raw SQLite API Bindings   ~20% faster  │
├─┘                                                                           │
├──┐                                                                          │
│L3│               Lighter, w/ high level API (baseline)            Baseline  │
├──┘                                                                          │
├─────┐                                                                       │
│GRDB │            with handwritten record mappings              ~50% slower  │
├─────┘                                                                       │
├─────────────┐                                                               │
│SQLite.swift │    with handwritten record mappings               >3× slower  │
├─────────────┘                                                               │
├───────────────────────┐                                                     │
│GRDB with Codable      │                                         >6× slower  │
├───────────────────────┘                                                     │
├───────────────────────────────────────────────────────────────────────────┐ │
│SQLite.swift with Codable                                       >19× slower│ │
├───────────────────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────────────┘
      Time to load the Northwind "Products" table 500 times into a model.      
                              Shorter is faster.

Main takeway: Never ever use Codable.

TODO

  • CoreData performance test (replicate schema, load records via Lighter)
  • Add a test for GRDB Codable
  • Add a test for SQLite.swift Codable
  • Add a test for SQLite.swift w/o Codable, with hand written mappings
  • Lighter API Tests
  • Enlighter generated raw SQLite API Tests

Test Environment

Northwind

  • Northwind SQLite
    • the larger one (make populate) is 24MB, the small 602KB
      • 9 employees
      • 93 customers
      • 16k orders, 560k details
      • 4 regions
$ cd /tmp
$ git clone https://github.com/jpwhite3/northwind-SQLite3
$ cd northwind-SQLite3
$ make build populate
rm -rf ./dist
mkdir ./dist
sqlite3 dist/northwind.db < src/create.sql > /dev/null
sqlite3 dist/northwind.db < src/update.sql > /dev/null
sqlite3 dist/northwind.db < src/report.sql
Categories|8
CustomerCustomerDemo|0
CustomerDemographics|0
Customers|93
EmployeeTerritories|49
Employees|9
Details|2155
Orders|830
Products|77
Regions|4
Shippers|3
Suppliers|29
Territories|53
python3 ./src/populate.py
sqlite3 dist/northwind.db < src/report.sql
Categories|8
CustomerCustomerDemo|0
CustomerDemographics|0
Customers|93
EmployeeTerritories|49
Employees|9
Details|588936      <==
Orders|15889        <==
Products|77
Regions|4
Shippers|3
Suppliers|29
Territories|53

$ ls -lh dist
-rw-r--r--  1 helge  wheel    23M Aug  6 11:05 northwind.db

$ sqlite3 -readonly dist/northwind.db 
SQLite version 3.37.0 2021-12-09 01:34:53
sqlite> SELECT COUNT(*) FROM "Orders";
15889
sqlite> SELECT COUNT(*) FROM "Order Details";
588936
CREATE TABLE [Orders](
  [OrderID]        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  [CustomerID]     TEXT,
  [EmployeeID]     INTEGER,
  [OrderDate]      DATETIME, -- 2019-02-10 04:59:33
  [RequiredDate]   DATETIME,
  [ShippedDate]    DATETIME,
  [ShipVia]        INTEGER,
  [Freight]        NUMERIC DEFAULT 0,
  [ShipName]       TEXT,
  [ShipAddress]    TEXT,
  [ShipCity]       TEXT,
  [ShipRegion]     TEXT,
  [ShipPostalCode] TEXT,
  [ShipCountry]    TEXT,
  
  FOREIGN KEY ([EmployeeID]) 
    REFERENCES [Employees] ([EmployeeID]) 
      ON DELETE NO ACTION ON UPDATE NO ACTION,
  FOREIGN KEY ([CustomerID]) 
    REFERENCES [Customers] ([CustomerID]) 
      ON DELETE NO ACTION ON UPDATE NO ACTION,
  FOREIGN KEY ([ShipVia]) 
    REFERENCES [Shippers] ([ShipperID]) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE [Order Details](
  [OrderID]   INTEGER NOT NULL,
  [ProductID] INTEGER NOT NULL,
  [UnitPrice] NUMERIC NOT NULL DEFAULT 0,
  [Quantity]  INTEGER NOT NULL DEFAULT 1,
  [Discount]  REAL    NOT NULL DEFAULT 0,
  
  PRIMARY KEY ("OrderID","ProductID"),
  CHECK ([Discount]>=(0) AND [Discount]<=(1)),
  CHECK ([Quantity]>(0)),
  CHECK ([UnitPrice]>=(0)),
  
  FOREIGN KEY ([OrderID]) 
    REFERENCES [Orders] ([OrderID]) 
      ON DELETE NO ACTION ON UPDATE NO ACTION,
  FOREIGN KEY ([ProductID]) 
    REFERENCES [Products] ([ProductID]) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
);

GRDB

  • GRDB
    • Add as package dependency 5.26.0
$ cd /tmp
$ git clone https://github.com/groue/GRDB.swift
$ cd GRDB.swift/
$ git checkout v5.26.0
$ du -sh .
232M  .
$ pushd Tests/Performance/GRDBProfiling/GRDBProfiling
sqlite> .schema
CREATE TABLE items (i0 INT, i1 INT, i2 INT, i3 INT, i4 INT, i5 INT, i6 INT, i7 INT, i8 INT, i9 INT);
sqlite> SELECT COUNT(*) FROM items;
100000

Lighter

  • Lighter.swift
    • Add as package dependency

SQLite.swift

Bun.sh

  • Bun is a new JavaScript runtime which claims to be extremely fast and has SQLite test specifically. It claims to be ~3x faster than Node.js w/ better-sqlite3 and 6x faster than Deno.
$ curl https://bun.sh/install | bash
bun was installed successfully to ~/.bun/bin/bun 

Manually add the directory to ~/.zshrc (or similar):
  export BUN_INSTALL="$HOME/.bun" 
  export PATH="$BUN_INSTALL/bin:$PATH" 

$ bun --version
0.1.7
$ git clone https://github.com/oven-sh/bun.git
$ cd bun/bench/sqlite
  • Adjust dbfile to point to "/tmp/northwind-SQLite3/dist/northwind.db"
  • Restrict test to "Orders" table (also rename from "Order")
$ bun query.js
cpu: unknown
runtime: bun 0.1.2 (arm64-darwin) 

benchmark                   time (avg)             (min … max)       p75       p99      p995
-------------------------------------------------------------- -----------------------------
SELECT * FROM "Orders"   13.04 ms/iter   (12.56 ms … 14.74 ms)  12.97 ms  14.74 ms  14.74 ms

That actually matches Lighter, it probably is the baseline SQLite3 performance with no mapping overheader (Lighter almost has zero, except for Swift String creation).

Presumably this doesn't include any garbage collection in bun, while the Swift tests free the records in each iteration via ARC.

Who

Lighter is brought to you by Helge Heß / ZeeZide. We like feedback, GitHub stars, cool contract work, presumably any form of praise you can think of.

Want to support my work? Buy an app: Past for iChat, SVG Shaper, Shrugs, HMScriptEditor. You don't have to use it! 😀