Re: [sqlite] Distinguishing between sqlite3_stmts
Roger Binns wrote: >> John Stanton has correctly pointed out that there is a programming model >> here an application effectively does the cacheing itself by precompiling > > statements at startup. > > That is not caching and it would be unaffected by any caching scheme. > sqlite3_finalize would never be called so nothing would enter the cache. > sqlite3_prepare would be called a few times but the queries would not > be in the cache. This is no different than today. > Correct, compiling all stratements in the application is appropriate for embedded insatllations. Cacheing only achieves a benefit when ad-hoc querires are processed and may be repetitive. This is a special case. A good approach would be to just have an extra level in the API and perform cacheing if appropriate by calling the higher level function. The architecture of Sqlite is highly supportive of such an approach. A public spritied user might program a set of higher level APIs which maintain a cache of prepared statements, perhaps in an MRU stack, and submit it to the community. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Douglas E. Fajardo wrote: > John Stanton has correctly pointed out that there is a programming model here > an application > effectively does the cacheing itself by precompiling statements at startup. > In this situation, > the proposed cacheing feature represents double work, and double memory (or > worse). Precompiling statements is not caching. There would not be double work even if the application implements its own caching. The way a cache would work is that the sqlite3 pointer would contain an extra data structure to store cached statements. There is already a hash type that is appropriate and would consume about 28 bytes of memory if empty. When sqlite3_prepare is called, the cache is consulted. If there is no entry then the current prepare is run. When sqlite3_finalize is called then instead of freeing, the statement is put in the statement cache. > John Stanton has correctly pointed out that there is a programming model > here an application effectively does the cacheing itself by precompiling statements at startup. That is not caching and it would be unaffected by any caching scheme. sqlite3_finalize would never be called so nothing would enter the cache. sqlite3_prepare would be called a few times but the queries would not be in the cache. This is no different than today. > In this situation, the proposed cacheing feature represents double work, and double memory (or worse). There is never double work. The point of a statement cache is to avoid calling prepare! If the application had a cache and SQLite had a cache then you still wouldn't have the same statement in both caches since it would be on eviction from the application cache that the SQLite cache would see a statement. There is one way to increase memory consumption, but only a bit. That is on prepare to add the statement to the cache immediately rather than waiting till finalize. If the same query is used again then you duplicate the statement in the cache. This can be mitigated by splitting the current sqlite3_stmt into two pieces. One piece is the byte code and other meta-data and the second piece is the data that changes while the statement executes (registers etc). If a statement is only in use once then the extra memory consumption would consist of a pointer between the two pieces. If the statement is used multiple times then you get a memory saving since the static piece would only exist once rather than per statement as is the case now. You would also save on the copy of the query string each sqlite3_prepare_v2 makes since you would only need it once. > My proposal of a statement cache, which Rodger Binns supports, (*Roger* - no 'd') I don't necessarily support a SQLite statement cache, but I do think it important the issue is discussed and whichever way things go is documented in the ticket so that it doesn't have to be discussed again :-) I already have a statement cache in my code (265 lines of executable code - no counting of comments, declarations etc). It works well, is threadsafe and re-entrant and handles multiple statements correctly. Getting everything right is hard especially the corner cases. (Just because it runs doesn't make it right!) If everyone is implementing their own statement cache then I strongly support SQLite doing it instead since it will be way better and not wasting every developer's time. Looking at the costs: * A hash table in sqlite3 * (about 28 bytes empty) * (Option A) A lazy cache that puts finalized statements into cache * (Option B) An eager cache that puts prepared statements into cache at prepare time, but splits into static and dynamic parts For a simple program that calls prepare at startup then the costs are negligible. Option A would never result in any entries (finalize not called). Option B would but each stmt would grow by a pointer and a reference count over today's stmt. If the same query is prepared multiple times (eg if it was used in multiple threads) then Option A has the same memory consumption and Option B *saves* memory. Since caches could be compiled out, or run time switchable that would satisfy everyone anyway. If caches are as prevalent as seems to be the case then that is a very strong argument for the code to be part of the core. As SQLite is being used in more and more places and wrapped for more and more languages and environments then the cache makes even more sense. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkYoQEACgkQmOOfHg372QT0jgCdEyXuvxrzbEZgtai+WwSlA2uT T5EAnApwMA69jgOhuPerQ07utRD5iqX0 =Zuue -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
Roger and John both, of course have good points - John Stanton has correctly pointed out that there is a programming model where an application effectively does the cacheing itself by precompiling statements at startup. In this situation, the proposed cacheing feature represents double work, and double memory (or worse). My proposal of a statement cache, which Rodger Binns supports, is based on the idea that an application does not store prepared statements. In this case, the performance benefits are clear. I have no doubt that both approaches are in common use, and neither is, IMHO, 'wrong'. It seems to me that a run time control (a pragma?) to enable/disable the feature would accommodate both program models. In addition, a compile-time feature macro to prevent the bloatware problems for embedded applications is in order. I believe the pragma should enable cacheing, so the default is with cacheing disabled. This makes the feature transparent to *all* existing programs. The compile-time Macro should default to include the cacheing feature. This makes the feature available normally. Those who are memory conscious will probably be rebuilding the library anyway, since it is likely that other compile-time changes would already be necessary to reduce the size of sqlite. *** Doug Fajardo -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Monday, November 10, 2008 8:15 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Distinguishing between sqlite3_stmts You make a argument for Bloatware. It is not oersuasive. JS Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > John Stanton wrote: > >>Perhaps this featrure could be reserved for "Sqlheavy", a replacement >>for Oracle. > > > Or a #if OMIT_STATEMENT_CACHE like all sorts of other functionality that > can be omitted. > > >>We have actually implemented the cacheing of prepared statements, and >>add it in the form of a local library which extends the Sqlite API. > > > If almost everyone is doing a statement cache then there is a stronger > argument for it being part of the core library. > > >>Cacheing compiled SQL is only helpful in certain types of application. > > > I'd argue that it is helpful in most applications that use bindings and > don't implement their own statement caching. > > >>In a typical emdedded application where all SQL is compiled as the >>application intializes and subsequent usage involves the binding of >>variables cacheing would be detrimental to performance and footprint. > > > Caching would increase memory for one off queries since unused > statements would be in the cache. In your scenario there would actually > be no difference since the cache would be empty. A SQLite hash table > with no entries is approximately 28 bytes. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkkV6qIACgkQmOOfHg372QTsOgCggRTY1TSkxHLznv95G64N+PjH > p34AoMHMjgGsxZTgufEVUz7hP6uM8/14 > =0HbU > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
Rodger: Thanks for creating the ticket. I will be interested to see the result when (if? :-) it gets implemented. Dan: It was my thought that a single, well thought-out implementation of caching (as opposed to something that each programmer implements on an 'ad-hoc' basis) would offer a speed advantage to all, and would even be source-code compatible for existing programs. As for internal vs. external, Do you mean 'external' as an add-on library that 'wraps' the prepare* statements? It could be done, but I'm not (personally) fond of the idea. Either some fancy linking is needed to replace the affected calls with 'new' versions (doesn't sound like fun when you want to do it across a wide range of platforms), or a new function call name (which is not source-compatible) would be necessarily. If by 'external' you meant 'in the API' as opposed to 'in the engine itself', I don't feel I have enough background in sqlite internals to express an opinion. *** Doug Fajardo -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dan Sent: Friday, November 07, 2008 9:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Distinguishing between sqlite3_stmts On Nov 8, 2008, at 3:25 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Douglas E. Fajardo wrote: >> ( To the 'powers that be'... I wonder if some form of 'cache' for >> prepared statements might be built in to the 'sqlite3_prepare*' >> functions as a performance enhancement? ) > > I couldn't find an existing ticket so created a new one: > > http://www.sqlite.org/cvstrac/tktview?tn=3483 Are there advantages to implementing this internally instead of externally? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
You make a argument for Bloatware. It is not oersuasive. JS Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > John Stanton wrote: > >>Perhaps this featrure could be reserved for "Sqlheavy", a replacement >>for Oracle. > > > Or a #if OMIT_STATEMENT_CACHE like all sorts of other functionality that > can be omitted. > > >>We have actually implemented the cacheing of prepared statements, and >>add it in the form of a local library which extends the Sqlite API. > > > If almost everyone is doing a statement cache then there is a stronger > argument for it being part of the core library. > > >>Cacheing compiled SQL is only helpful in certain types of application. > > > I'd argue that it is helpful in most applications that use bindings and > don't implement their own statement caching. > > >>In a typical emdedded application where all SQL is compiled as the >>application intializes and subsequent usage involves the binding of >>variables cacheing would be detrimental to performance and footprint. > > > Caching would increase memory for one off queries since unused > statements would be in the cache. In your scenario there would actually > be no difference since the cache would be empty. A SQLite hash table > with no entries is approximately 28 bytes. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkkV6qIACgkQmOOfHg372QTsOgCggRTY1TSkxHLznv95G64N+PjH > p34AoMHMjgGsxZTgufEVUz7hP6uM8/14 > =0HbU > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John Stanton wrote: > Perhaps this featrure could be reserved for "Sqlheavy", a replacement > for Oracle. Or a #if OMIT_STATEMENT_CACHE like all sorts of other functionality that can be omitted. > We have actually implemented the cacheing of prepared statements, and > add it in the form of a local library which extends the Sqlite API. If almost everyone is doing a statement cache then there is a stronger argument for it being part of the core library. > Cacheing compiled SQL is only helpful in certain types of application. I'd argue that it is helpful in most applications that use bindings and don't implement their own statement caching. > In a typical emdedded application where all SQL is compiled as the > application intializes and subsequent usage involves the binding of > variables cacheing would be detrimental to performance and footprint. Caching would increase memory for one off queries since unused statements would be in the cache. In your scenario there would actually be no difference since the cache would be empty. A SQLite hash table with no entries is approximately 28 bytes. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkV6qIACgkQmOOfHg372QTsOgCggRTY1TSkxHLznv95G64N+PjH p34AoMHMjgGsxZTgufEVUz7hP6uM8/14 =0HbU -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
Dan wrote: > On Nov 8, 2008, at 3:25 AM, Roger Binns wrote: > > >>-BEGIN PGP SIGNED MESSAGE- >>Hash: SHA1 >> >>Douglas E. Fajardo wrote: >> >>> ( To the 'powers that be'... I wonder if some form of 'cache' for >>>prepared statements might be built in to the 'sqlite3_prepare*' >>>functions as a performance enhancement? ) >> >>I couldn't find an existing ticket so created a new one: >> >> http://www.sqlite.org/cvstrac/tktview?tn=3483 > > > Are there advantages to implementing this internally instead of > externally? > Perhaps this featrure could be reserved for "Sqlheavy", a replacement for Oracle. We have actually implemented the cacheing of prepared statements, and add it in the form of a local library which extends the Sqlite API. Cacheing compiled SQL is only helpful in certain types of application. In a typical emdedded application where all SQL is compiled as the application intializes and subsequent usage involves the binding of variables cacheing would be detrimental to performance and footprint. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
-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
Re: [sqlite] Distinguishing between sqlite3_stmts
On Nov 8, 2008, at 3:25 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Douglas E. Fajardo wrote: >> ( To the 'powers that be'... I wonder if some form of 'cache' for >> prepared statements might be built in to the 'sqlite3_prepare*' >> functions as a performance enhancement? ) > > I couldn't find an existing ticket so created a new one: > > http://www.sqlite.org/cvstrac/tktview?tn=3483 Are there advantages to implementing this internally instead of externally? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Douglas E. Fajardo wrote: >( To the 'powers that be'... I wonder if some form of 'cache' for prepared > statements might be built in to the 'sqlite3_prepare*' functions as a > performance enhancement? ) I couldn't find an existing ticket so created a new one: http://www.sqlite.org/cvstrac/tktview?tn=3483 If a statement cache existed internal to SQLite then it would also be possible to implement things like this: http://www.sqlite.org/cvstrac/tktview?tn=739 Both the Python wrappers for SQLite implement a statement cache. A SQLite statement cache would actually be slower as they both use the Python level string object as the cache key, with APSW also using the UTF8. SQLite would only have the UTF8 available. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkUpBYACgkQmOOfHg372QSdogCeObfOOI2HoyXMkT3IayIehUuk ClUAn2zfqnr+3MBXe45PdQWdwfYCbvuY =iYqs -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
If I am understanding you correctly, you are attempting to 'cache' prepared statements. My solution was to implement a wrapper function around the SQLITE3_PREPARE call function that stored pointers to the sql statement, the database handle, and the 'prepared' statement in a linked list. On subsequent calls the function would return the already prepared statement if one was present. The search of the list was based on the SQL text of the query. Various techniques such as using a hash or checksum of the SQL text as the initial search key will speed up the search. ( To the 'powers that be'... I wonder if some form of 'cache' for prepared statements might be built in to the 'sqlite3_prepare*' functions as a performance enhancement? ) Hope this helps. *** Doug Fajardo -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Thursday, November 06, 2008 4:54 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Distinguishing between sqlite3_stmts On Nov 6, 2008, at 7:47 PM, Brown, Daniel wrote: > Good afternoon list, > > Are there any methods for distinguishing between sqlite3_stmt > structures > prepared by sqlite3_prepare_v2? I'd like to be able to tell if a > statement structure has been finalized and then prepared with a > different query programmatically. Is there any sort of unique > identifier in those prepared structures? I'm not sure I understand the question. Is http://www.sqlite.org/c3ref/sql.html what you are asking for? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
Almost, I want to be able to compare sqlite3_stmt structures somehow and be able to work out if given two pointers to a sqlite3_stmt are pointing at the same query instance or not, as the address pointed to is not sufficient. In the C++ system I have results objects are returning values from SQLite queries via an internal pointer to a sqlite3_stmt held in another C++ query object which the queries objects have a reference to. As it is possible to have multiple results objects per query object, I am concerned that if the sqlite3_stmt (in the query object) was recycled via finalize/prepare that it would mess up the remaining results objects that were pointing to it unless those result objects could detect the change of query themselves. Thinking about it the function you link to would probably be sufficient. If I either store the original query string or a hash of the query string locally in each result object (as space is at a premium a hash would be preferred). I could then compare the value stored in the results object against the value in the query object to determine if the query object had been recycled or not... Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Thursday, November 06, 2008 4:54 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Distinguishing between sqlite3_stmts On Nov 6, 2008, at 7:47 PM, Brown, Daniel wrote: > Good afternoon list, > > Are there any methods for distinguishing between sqlite3_stmt > structures > prepared by sqlite3_prepare_v2? I'd like to be able to tell if a > statement structure has been finalized and then prepared with a > different query programmatically. Is there any sort of unique > identifier in those prepared structures? I'm not sure I understand the question. Is http://www.sqlite.org/c3ref/sql.html what you are asking for? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
On Nov 6, 2008, at 7:47 PM, Brown, Daniel wrote: > Good afternoon list, > > Are there any methods for distinguishing between sqlite3_stmt > structures > prepared by sqlite3_prepare_v2? I'd like to be able to tell if a > statement structure has been finalized and then prepared with a > different query programmatically. Is there any sort of unique > identifier in those prepared structures? I'm not sure I understand the question. Is http://www.sqlite.org/c3ref/sql.html what you are asking for? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Distinguishing between sqlite3_stmts
Good afternoon list, Are there any methods for distinguishing between sqlite3_stmt structures prepared by sqlite3_prepare_v2? I'd like to be able to tell if a statement structure has been finalized and then prepared with a different query programmatically. Is there any sort of unique identifier in those prepared structures? Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
