|Title:||Prepared statement cache|
|Last Modified:||2013-12-31 18:29:27|
|Version Found In:||3.6.4|
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.
rogerb added on 2009-10-11 07:36:09:
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.