SQLite

View Ticket
Login
Plaintext
Ticket Hash: ee4b2b48f5080f7eaead1d5e08eb6af25d366916
Title: Prepared statement cache
Status: Closed Type: Feature_Request
Severity: Cosmetic Priority: Low
Subsystem: Parser Resolution: Rejected
Last Modified: 2022-11-11 13:51:34
2.49 years ago 		Created: 2009-10-11 06:43:09
15.57 years ago
Version Found In: 3.6.4
Description:
Various wrappers and users of SQLite have implemented their own statement caches. Arguably such functionality is best implemented in SQLite itself. A proposed implementation is an additional hash table on sqlite3* to which statements are added when sqlite3_finalize is called instead of actually finalizing them. On prepare look in the hash table for a match and reuse else do regular prepare. Add some code in finalize to ensure the cache doesn't grow too large.

http://www.sqlite.org/cvstrac/tktview?tn=3483 Mailing list post

rogerb added on 2009-10-11 07:36:09:
As a datapoint, using my benchmark derived from one in SQLite, I get a 15% performance improvement using a home written statement cache and a 3% performance decrease when the cache is being used but there are never any matches (statements have random values).

User Comments:
anonymous added on 2013-12-31 15:24:44:

The figure of fifteen percent improvement is arbitrary. It depends on the complexity of the queries and the time to get the results. In a case with complex queries, of 1000 characters on average, that are optimized to return very fast, I measured a cmpletely different figure. My (APSW-) application took four times longer for the same run after disabling the statement cache. Thus the improvement by the cache is 75 percent.

anonymous (claiming to be rogerb) added on 2013-12-31 18:29:27: 
The results will vary by benchmark.  The speed test I did to get those numbers is documented at http://rogerbinns.github.io/apsw/benchmarking.html#speedtest and is a Python conversion of TCL code used by SQLite itself for speed testing.
drh added on 2022-11-11 13:51:34:

There is an experimental branch to support a prepared statement cache at stmt-cache. But that effort was abandoned because I could not find a benefit sufficient to justify the added complexity.