1

I was asked to improve existing code to query SQLite databases. The original code made a lot of separate calls to the database and filtered the results in Python. Instead, I opted to re-write the database creation and put the filtering logic in the SQL query.

After running benchmarks on a databases of different sizes. While comparing with the original implementation I found that the average query time for n=3 of a query was a lot faster in the new implementation (3s vs. 46 minutes). I suspected that this was a caching issue, but I wasn't sure of its origin. Between every query I closed the database connection and deleted any lingering Python variables and ran gc but the out-of-this-world persisted. Then I found that it was likely the system that was caching something. Indeed, when I clear the system's cache after every iteration with echo 3 > /proc/sys/vm/drop_caches the performance is much more in line with what I expected (2-5x speed increase compared to 80.000x speed increase).

The almost philosophical issue that I have now is what I should report as an improvement: the cached performance (as-is) or the non-cached performance (explicitly deleting cache before queries). (I'll likely report both but I am still curious about what is being cached.) I think it comes down to the question what is actually being cached. In other words: does the caching represent a real-world scenario or doesn't it at all.

I would think that if the database or its indices are cached, then the fast default performance is a good representation of the real world as it would be applicable to new, unseen queries. However, if specific queries are cached instead, then the cached performance does not reflect on unseen queries.

Note: this might be an unimportant detail but I have found that the impact of this caching is especially noticeable when using fts5 virtual tables!

Tl;dr: when the system is caching queries to SQLite, what exactly is it caching, and does that positively impact new, unseen queries?

If it matters: Ubuntu 20.04 with sqlite3.

2
  • You actually observed that dropping system caches results in a significant speed increase of the queries ???
    – MC68020
    Commented Aug 7, 2022 at 12:23
  • No, the 2-5x speed increase is less than the first, absurdly faster, 80.000x speed increase compared to the original implementation. I clarified that, as it was indeed ambiguous. Commented Aug 7, 2022 at 12:25

1 Answer 1

1

No, it does not cache queries. It caches pages.

Database keeps the table (as well as indexes) in pages. Each page contains from one to multiple rows of the table. Once the page is in cache it can be used by any query which would require a row from that page.

Same goes for index: if a new query has a restriction on somefield between 20 and 40 - the database engine first look in its cache - does cache contains pages of that index which describe this range of values or not?

The page size is defined when you create the new database. Look at the documentation for pragma page_size on restrictions and how to use it.

That approach allows to share pages not just between unrelated queries, but even between different connections. Here is documentation on how that works: https://www.sqlite.org/sharedcache.html

5
  • So I think this is good news, as it is quite likely that queries match previous pages I believe. Considering that the caching occurred naturally without me explicitly setting it, this makes implementation easy. However, I read that the page size is 4096 bytes. Isn't that incredibly small to remember filtered rows from sub-queries? Commented Aug 7, 2022 at 12:31
  • I repeat again: the cache works on table pages, not filtered rows. And as for subqueries - that is done by creating temporary tables, which are in turn cached as usual.
    – White Owl
    Commented Aug 7, 2022 at 12:38
  • A better question is, why did the python code not also benefit from caching? Either it used so much memory that it pushed cache pages out, or it queried too much from the database pushing pages out. Either one of these is a real world win for the new code (unless you buy more memory).
    – user10489
    Commented Aug 7, 2022 at 13:28
  • In the original post you said, "a lot of separate calls to the database and filtered the results in Python". What are those "separate calls"? If the client code downloads a whole table and then does filtering on it - then there is no cache involvement at all. It is just not used...
    – White Owl
    Commented Aug 8, 2022 at 11:21
  • Yes, that was my point. If the client is dumb enough to download the whole table and filter it then any cache would be totally flushed downloading the whole table. An 80x speed increase would not be surprising just from letting sql do the filtering instead and not polluting cache memory with unneeded data. That's half the point of sql.
    – user10489
    Commented Aug 8, 2022 at 11:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .