Two dead simple pragmas for boosting query performance

[mallman]

I'd like to share a couple of dead simple SQLite performance improvements I've used to great effect. These tips require no changes to your queries. They're just SQLite pragmas. I'll include instructions for using these with GRDB at the bottom.

By setting pragma temp_store = MEMORY, I've improved the performance of some of my most complex SELECT queries by ~68% without any apparent side effects. These are queries with multiple nested subqueries (in FROM), windows, filtering, grouping, ordering, limit and offset. However, I've seen no performance improvement to simpler SELECT queries.

By additionally setting pragma cache_size = -1000000, I've seen more modest but measurable performance improvements up to about 5% in run time. However, I've seen these performance improvements across a wide variety of SELECT queries, including those for which pragma temp_store = MEMORY have no effect.

As is the case with any SQLite pragmas, I do recommend reading the documentation to better understand them.

You can enable these pragmas on a case-by-case basis using

try db.execute(sql: "pragma cache_size = -1000000")
try db.execute(sql: "pragma temp_store = MEMORY")

You can also enable them for all transactions by updating your GRDB connection Configuration:

let dbConfig: Configuration = ...
dbConfig.prepareDatabase { db in
  try db.execute(sql: "pragma cache_size = -1000000")
  try db.execute(sql: "pragma temp_store = MEMORY")
}

I will note that I'm using GRDB in a macOS app with a custom build of SQLite 3.35.5. I've found using a custom SQLite build with release optimization to be substantially faster than using Apple's closed source version of SQLite. GRDB has instructions for building GRDB with a custom SQLite.

I'd love to hear if you've found these performance tips to be useful in your own apps. Are they useful in iOS apps, too?