March summary

Finishing line

By Jonas Bengtsson

Apr 02, 2023

The main focus during March has been on improving the performance, especially for very large data sets. By importing a data set with 450 000+ entities the bottlenecks have been much more apparent. Especially for SQLite which I haven't spent too much optimisation on in the past, mostly because it's very fast out of the box.

Benchpress, the benchmarking library, is now automatically calling console.profile()/console.profileEnd() around all iterations, after warmup, to facilitate getting CPU profile e.g. when running the benchmarks in the VS Code debugger.

I've also added some callbacks, DatabasePerformanceCallbacks, to the database adapters in order to extract timings of various database operations. This allows exposing information to the browser using the Server-Timing header (which browsers display in the dev tools). It also allows to log long running queries, which helps in debugging.

There were several SQL queries that took > 30 seconds on SQLite before optimization. Most could be fixed with straightforward indexes, but the search/sample queries required a rewrite using a WITH clause (a Common Table Expression/CTE) to get fast.

The GraphQL schema has received some tweaks. The naming is now more consistent, so the EntityType, ValueType, QueryOrder types now has a Published prefix (to match the corresponding types with Admin prefix). And searchEntities is now called publishedSearchEntities. Location is now using a scalar to simplify the queries.

And as always, a bunch of dependencies were updated :-)