Implementing a statement cache

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Implementing a statement cache

carsten.muencheberg
Hi,
I am working on a generic cache for prepared statements and would like
to make sure that I am not overlooking anything important.

The cache is a simple map from an SQL string to a statement pointer.

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. 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?

2. When to call sqlite3_clear_bindings()? If I understand correctly new
values can be bound without clearing old ones first, but calling
sqlite3_clear_bindings() can be a safeguard against accidentally
executing a statement with old values?

3. When to clear the cache? I read that in some cases statements are
automatically recompiled when a different value is bound to a parameter
inside the WHERE clause. What about SQLITE_ENABLE_STAT4 and ANALYZE,
would it make sense to clear the cache afterwards or can we trust SQLite
to maintain existing statements under all circumstances?

4. Other ideas, comments?

Thanks in advance.
Carsten
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Implementing a statement cache

Hick Gunter
Any statement that has been stepped but not to completion will hold open the transaction on a connection. This may interfere with your expectations. Clearing bindings as a precaution will prevent inadvertent re-use of old bindings. The statement may have to be reset first, see documentation. Statements prepared with the V2 (or later) interface will recompile if necessary.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von carsten.muencheberg
Gesendet: Montag, 16. Dezember 2019 22:38
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Implementing a statement cache

Hi,
I am working on a generic cache for prepared statements and would like to make sure that I am not overlooking anything important.

The cache is a simple map from an SQL string to a statement pointer.

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. 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?

2. When to call sqlite3_clear_bindings()? If I understand correctly new values can be bound without clearing old ones first, but calling
sqlite3_clear_bindings() can be a safeguard against accidentally executing a statement with old values?

3. When to clear the cache? I read that in some cases statements are automatically recompiled when a different value is bound to a parameter inside the WHERE clause. What about SQLITE_ENABLE_STAT4 and ANALYZE, would it make sense to clear the cache afterwards or can we trust SQLite to maintain existing statements under all circumstances?

4. Other ideas, comments?

Thanks in advance.
Carsten
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Implementing a statement cache

E.Pasma
In reply to this post by carsten.muencheberg
> Op 16 dec. 2019, om 22:38 heeft carsten.muencheberg <[hidden email]> het volgende geschreven:
>
> Hi,
> I am working on a generic cache for prepared statements and would like to make sure that I am not overlooking anything important.
>
> The cache is a simple map from an SQL string to a statement pointer.
>
> 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. 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?
>
> 2. When to call sqlite3_clear_bindings()? If I understand correctly new values can be bound without clearing old ones first, but calling sqlite3_clear_bindings() can be a safeguard against accidentally executing a statement with old values?
>
> 3. When to clear the cache? I read that in some cases statements are automatically recompiled when a different value is bound to a parameter inside the WHERE clause. What about SQLITE_ENABLE_STAT4 and ANALYZE, would it make sense to clear the cache afterwards or can we trust SQLite to maintain existing statements under all circumstances?
>
> 4. Other ideas, comments?
>
> Thanks in advance.
> Carsten
Hi, as nobody answers yet to the real questions, I just mention that a statement cache is in tclsqlite3.c. That may be a useful example. It is also in the apsw python interface. Regards, E. Pasma


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Implementing a statement cache

Roger Binns
In reply to this post by carsten.muencheberg
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


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (201 bytes) Download Attachment