On 16/12/2019 13:38, carsten.muencheberg wrote:
> I am working on a generic cache for prepared statements
I really wish SQLite had a statement cache behind the scenes, so that
devs don't have to keep re-implementing a statement cache. I would be
delighted to delete the statement cache code in APSW.
> The cache is a simple map from an SQL string to a statement pointer.
Note that you will need SQLite to parse the input string to get the SQL
string. For example your API could be called with "select 3; select 4;"
and would need to be broken in the middle into two statements. You need
to get all this stuff right.
I used the same map approach which requires you keeping an in-use flag
for the statement pointer. This is because you may be supplied the same
SQL twice without the first being released yet. My implementation only
has one statement per SQL text meaning additional executions of the same
SQL do not use the cache.
There are multiple copies of the SQL text too. One copy will be in
whatever calls you, you need a copy to use for the key in the map, and
then SQLite internally keeps a third copy. You can avoid that third
copy by using the v1 prepare method and handling SQLITE_SCHEMA yourself.
It would be so much better if SQLite had the cache internally.
> 1. When to call sqlite3_reset()? It looks like the safest and easiest
> approach is to call sqlite3_reset() immediately after retrieving a
> statement from the cache.
Do so immediately when you are finished with the statement (eg about to
put it back in the cache). That will release all the locks etc, as well
as free memory - eg if a binding is a long string or blob.
> Is there any disadvantage in regards to
> concurrency or performance in keeping dozens or hundreds of statements
> alive in a non reset state e.g. SELECT statements which have not stepped
> over all rows?
You will have considerably more memory allocated, in addition to held
locks etc. Note that cleanup is going to happen. You could do it all
at the very end, or I prefer to do it as soon as possible to keep the
footprint more compact. The cache is supposed to be transparent.
> 2. When to call sqlite3_clear_bindings()?
Same thing - the sooner the better.
> but calling
> sqlite3_clear_bindings() can be a safeguard against accidentally
> executing a statement with old values?
If the cache is transparent then you must do so to avoid very hard to
diagnose bugs.
> 3. When to clear the cache?
My implementation has the developer specify the number of entries in the
cache (default 100). In addition to the mapping between SQL text and a
statement, there is a linked list between the statements tracking least
recently used. This is a fairly complex combined data structure, and
another reason SQLite should do it (one place to get right).
> I read that in some cases statements are automatically recompiled
This is not relevant to a statement cache, and if you use the currently
documented APIs it is something you do not need to know or care about ever.
Behind the scenes each statement is transformed into byte code which is
what SQLite runs to perform a query. This is necessary because you get
a result row at a time, so SQLite has to be able to suspend and resume
execution. https://sqlite.org/vdbe.html
For example the vdbe will mention column 3, and if the table schema has
changed, the same named column could now be column 4. In the olden
days, if you tried to execute the vdbe SQLite would detect it was out of
date, return an error code (SQLITE_SCHEMA) and the developer would have
to reprepare the statement. Now SQLite keeps a copy of the SQL text and
does the reprepare internally and transparently.
> 4. Other ideas, comments?
Keep asking the SQLite team to make an internal SQLite statement cache.
I'd be happy to call different APIs even.
Roger
signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
