Slow Latency on First Query

[JonathanHope]

Love the idea behind this!

I've noticed some surprising latency the first time a query is run (db.Query). On my machine it takes about 400ms. Every call to db.Query afterwards returns in comparable time to other drivers.

It's like there is some kind of expensive one time setup that occurs at that point. I don't know if this is expected behavior or not.

Thanks!

[ncruces]

This is a good question that I'd like to keep highlighted here, rather than in a closed issue.
Hope you don't mind!

[ncruces]

Yes, this is expected behaviour, though maybe it should be documented better.

We embed a Wasm version of SQLite. This needs to be compiled to machine code on the first instantiation:

go-sqlite3/sqlite.go

Lines 70 to 71 in 58c5009

	func instantiateSQLite() (sqlt *sqlite, err error) {
	instance.once.Do(compileSQLite)

wazero (our WASM runtime) is quite fast at compiling, but the WASM file is also quite big (1.4Mb), hence the slowdown.

There are 3 ways to mitigate this cost:

1. compile at a more appropriate time
2. cache the compilation result
3. slim down SQLite to reduce compilation time

Compile at a more appropriate time

This should do the job:

if err := sqlite3.Initialize(); err != nil {
	log.Fatal(err)
}

You can also do this in a background goroutine.

Cache the compilation result

You can provide your own wazero.RuntimeConfig, by setting sqlite3.RuntimeConfig. This allows you to configure the compilation cache, choose between the compiler/interpreter, limit memory, etc…) The cache can speed up first instantiation by an order of magnitude.

Slim down SQLite to reduce compilation time

If you can do away with some of the optional features, one way to improve the situation is to generate your own, slimmed down, sqlite3.wasm. You can also trade speed for size (-O3 vs -Os).

All you need is wasi-sdk, and binaryen. You can edit sqlite_opt.h to remove some of the extensions (FTS, R*Tree, GeoPoly are good candidates if you don't need them). And tweak compile options in the build.sh script. To load your own build of SQLite, you can change either the Binary or Path variables, instead of importing embed.