Does SQLite cache results even after commit/close?

Question

I wish to run some benchmarks on different databases that I have. I repeat every query n times so that I can report average query times. I am aware that SQLite caches statements, as per the documentation:

The sqlite3 module internally uses a statement cache to avoid SQL parsing overhead. If you want to explicitly set the number of statements that are cached for the connection, you can set the cached_statements parameter. The currently implemented default is to cache 100 statements.

However, it is unclear to me whether this cache persists: so in short, does the SQLite cache persists (1) within a Python session even after closing the connection? (2) Across Python sessions (so cache written to disk?)?

My code looks something like this:

times = []
for i in range(n_repeat):
    start = time.perf_counter()
    conn = sqlite3.connect(dbpath)
    # do query
    conn.commit()
    conn.close()
    times.append(time.perf_counter() - start)

return timedelta(seconds=sum(times) / n_repeat)

My assumption was that whenever I close the connection, any and all caching would be discarded and garbage-collected immediately. I find little variance in the n runs (no difference between 1st and nth iteration), so I would think that my assumption is correct. But I'd rather be sure so I am asking here.

tl;dr: does SQLite cache queries even after a connection has closed? And does it cache queries across Python sessions?

That's *python* caching prepared statements instead of letting you manage them yourself. Nothing to do with Sqlite's own cache, btw. — Shawn, Aug 05 '22 at 09:43
And since a C-level prepared statement has to be finalized to let a database connection fully close, I doubt python keeps them after closing the db. — Shawn, Aug 05 '22 at 09:45

Bram Vanroy · Accepted Answer · 2022-10-12T15:13:24.547

After some digging I found that apart from caching within Python/SQLite (as Shawn mentions in the comments) a huge impactful factor was the OS-wide caching that occurs. I am not sure what exactly is cached (the database, the indices, the query itself) but if I delete system caches after every iteration (so no caching should occur) then I find that consecutive calls are much slower (in the order of 100x or more!).

So, yes SQLite is caching statements through Python but after closing the database these are released. But in addition, system caches will play a huge role.

Does SQLite cache results even after commit/close?

1 Answers1