Caching prepared statements in the SQLite Tcl Interface

Question

This question concerns coding properly in Tcl to best take advantage of SQLite's resetting of prepared statements rather than finalizing them.

Is there any difference, in regards to caching prepared statements, between code like

set ::SQL::QueryName { update ... }
dbcmd eval $::SQL::QueryName

and

dbcmd eval { update ...}

Or is it simply: in whatever manner the SQL string gets passed to SQLite, it'll store the last 10 or whatever value is set in dbcmd cache size N up to a maximum of 100?

Is there anything the coder can/should do to assist SQLite in this area?

Thank you for considering my question.

I forgot I should have added to this question, how does using the : symbol binding parameters affect caching? For example, if two queries are identical except for in a WHERE clause like data_key = :starKey versus data_key = :endKey, will these be considered one or two queries for caching purposes?

It could be written data_key = :dataKey and just set dataKey $startKey or set dataKey $endKey before executing the query.

Is there somewhere that this kind of information is listed? Thank you.

Answer 1

You do not need to take any special steps to cache prepared statements in the Tcl interface to SQLite. The interface library manages such a cache (with an LRU expiration rule) for you behind the scenes already (using the literal text of the SQL as its key). You can control the number of statements cached via the cache method of the connection; it defaults to being of size 10 and can be set to hold up to 100 statements.

db cache size 25
db eval { ... }

(I've never personally had the need to explicitly set the cache size.)

This is a really common aspect in how Tcl works: transparent caches where relevant (with the usual locus of the cache being inside the value instance, though not so in tclsqlite). It means that many programs end up storing cached compilations of things, either indexed by or attached to their literal, and all without any special action by user code. (Regular expressions cache this way, and so too do most evaluated script fragments.)