-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Dan wrote: >> http://www.sqlite.org/cvstrac/tktview?tn=3483 > > Are there advantages to implementing this internally instead of > externally?
Firstly there is an advantage to having a statement cache. I use a benchmark based on http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/mkspeedsql.tcl Using a statement cache of 100 entries saves about a second of runtime over a test that takes about 12 seconds to run. (I use :memory: database and set other params so everything is done in memory and the hard disk is not involved) Having a large cache with almost no hits (eg 10,000 entry cache and not using bindings) adds about 5 seconds to the run so the cache isn't a magic performance elixir. Note however that all these measurements include some Python overhead. The advantages of implementing internal to SQLite: - - The code in SQLite is going to be better and take care of all the corner cases than implementing the same functionality across numerous programs using SQLite. For example SQLite is far more likely to get the code right in multi-threaded programs and will have better testing. - - SQLite will be aware of the memory consumption and can do the "right thing" - - SQLite can do further optimizations such as caching query results and will know when they are invalidated etc. - - It would be transparent. Any user of SQLite gets the functionality for free. The disadvantages of implementing internal to SQLite: - - Other programming languages and libraries have their own string objects and so can use their string objects as a cache key. SQLite would always require a conversion to UTF8/16 first. - - The code gets bigger and has more ifdef's to omit the functionality - - It can make things worse as it is unaware of the big picture. For example if the cache is 100 entries in size and the application runs 101 different queries in sequence repeatedly then the cache won't help and you'll just waste CPU maintaining the cache and consume extra memory having these things hanging around. It may be worth looking at the performance of sqlite3_prepare. (cachegrind and kcachegrind worked well for me). The better the performance of prepare, the lower the advantage of a cache. As another data point, pretty much all the regular expression libraries out there are doing the same thing, compiling the string regular expression into a state machine. To my knowledge they all ended up with some sort of cache. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkVMR0ACgkQmOOfHg372QTkEwCeKRgZVIp2e9nL4YFY62mr2o/r w6AAniv6X7iexkCrz9ymP5PQVptX4Z7P =dkz+ -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
