Re: [sqlite] Pre-preparing querys
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 19/05/14 21:09, James K. Lowden wrote: > I took "statement cache" to mean that execution plans would persist > either past sqlite3_exec() or that many plan would be kept, in case > later useful, when sqlite3_step() recompiles according to passed > parameters. I think that's unlikely to help much, and might hurt. Statement cache means something front-ending sqlite3_prepare. A simple implementation would be updating sqlite3_finalize to stash the statement in a (bounded) hash table keyed by the SQL. Then in sqlite3_prepare look in the hash table for the SQL and return the stashed statement on match. (There are a few more subtleties.) > You're talking about using one prepared statement repeatedly, which, > if nothing else, is convenient. Statement cache sizes seem to be between 10 and 100 entries in various implementations I looked at. > And the application can always maintain its "cache" of > prepared-statement handles. Ah, for the olden days :-) Where exactly would this cache live? What if you are using several different libraries and modules that don't know about each other? What about various ORMs? The olden days where the app developer is responsible for every line of code in the app and they all work together well, including cooperating over a SQLite statement cache are a rarity now. Ask these questions: - - Should app/library developers using SQLite have to implement their own caching mechanism? - - Why did the majority of developers writing wrappers for SQLite spend the extra effort to also implement caches? BTW my answer for the second one is because statement preparation showed up in profiling, both outside and inside SQLite. The outside bit was because of having to do UTF8 conversion from native string representation, and inside because statement preparation takes a while - it involves parsing, many memory allocations and lots of other fiddly stuff. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlN7rRMACgkQmOOfHg372QRVoQCgtNdWJ/LiD67W2O7sdVSGinbV mvQAniz4mbJr3+8pzYj0siG5v+jjj+Ko =SB5Q -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
On 05/20/2014 07:30 AM, James K. Lowden wrote: On Mon, 19 May 2014 22:26:29 +0100 Simon Slavin wrote: On 19 May 2014, at 10:21pm, Roger Binns wrote: It seems like most language wrappers for SQLite include some sort of statement cache because it is generally useful. It seems like the sort of thing that would be helpful within the core of SQLite itself, or as an officially supported extra extension. Are there any advantages to this other than increased speed ? I'm going to go out on a limb and say No, and moreover it's not obvious there would be any improvement. Do we have statistics on the query planner's performance? How much improvement could the user (reasonably) hope for? I would expect the answer to be Not Much. On a modern computer I/O dominates everything, including SQL parsing. I expect that's true on many (all?) systems, but not all queries cause any actual I/O. Often the entire database sits in the OS cache or SQLite pager cache, so querying the database just means shuffling bytes around in main memory. I vaguely recall that at one point for the simplest possible queries: SELECT * FROM t1 WHERE rowid = ? if the database is in already memory the prepare() and step() calls are roughly similar in terms of real time. And most of that is the implicit transaction - executed within a BEGIN/COMMIT block the prepare() step is much more expensive than the step(). I'm not sure about more complicated queries. But I suspect it's very easy to find cases where the prepare() is at least as heavy as the step(). Dan. In theory very complex queries would be the exception, except that query planners long ago developed heuristic shortcuts. Mathematically, an N-way join is a combanatorial problem with N! solutions. When N is, say, 8, that's a lot to consider, 40,320 alternatives. A shortcut in such a case becomes the only cut. Even the perfect plan, by the way, may not be worth preserving. As the developers know, No battle plan survives first contact with the enemy. --Helmuth von Moltke meaning that any query plan, no matter how fine, is based only on the state of the data at the time. Change the data enough and it becomes slow, or useless. That's just an outline of the general case. I'll be interested to see what we know about SQLite's in particular. --jkl ___ 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] Pre-preparing querys
On Mon, 19 May 2014 20:35:54 -0400 Richard Hipp wrote: > > > On 19 May 2014, at 10:21pm, Roger Binns > > > wrote: > > > > > > > It seems like most language wrappers for SQLite include some > > > > sort of statement cache because it is generally useful. It > > > > seems like the sort of thing that would be helpful within the > > > > core of SQLite itself, or as an officially supported extra > > > > extension. > > > > > > Are there any advantages to this other than increased speed ? > > > > I'm going to go out on a limb and say No, > > > The cache is very useful when you want to execute a query like the > following 1 million times, with different bindings each time. > > INSERT INTO tab1 VALUES($a, $b, $c); Are we talking about the same thing? You're talking about using one prepared statement repeatedly, which, if nothing else, is convenient. And the application can always maintain its "cache" of prepared-statement handles. Whether the prepare step actually saves much relative to an INSERT is not obvious to me. On a machine that can execute, say, 25,000 INSERTs per second, wouldn't you expect at least 100X that number of prepares? I took "statement cache" to mean that execution plans would persist either past sqlite3_exec() or that many plan would be kept, in case later useful, when sqlite3_step() recompiles according to passed parameters. I think that's unlikely to help much, and might hurt. While I have your ear, though, do you have any rule-of-thumb about query compilation time? --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
On Mon, May 19, 2014 at 8:30 PM, James K. Lowden wrote: > On Mon, 19 May 2014 22:26:29 +0100 > Simon Slavin wrote: > > > On 19 May 2014, at 10:21pm, Roger Binns wrote: > > > > > It seems like most language wrappers for SQLite include some sort of > > > statement cache because it is generally useful. It seems like the > > > sort of thing that would be helpful within the core of SQLite > > > itself, or as an officially supported extra extension. > > > > Are there any advantages to this other than increased speed ? > > I'm going to go out on a limb and say No, The cache is very useful when you want to execute a query like the following 1 million times, with different bindings each time. INSERT INTO tab1 VALUES($a, $b, $c); > and moreover it's not obvious > there would be any improvement. Do we have statistics on the query > planner's performance? How much improvement could the user > (reasonably) hope for? > > I would expect the answer to be Not Much. > > Query plan caches are leftovers from the days when the ratio of I/O > speed to compute speed was much closer, by a few orders of magnitude. > On a modern computer I/O dominates everything, including SQL parsing. > > In theory very complex queries would be the exception, except that query > planners long ago developed heuristic shortcuts. Mathematically, an > N-way join is a combanatorial problem with N! solutions. When N is, > say, 8, that's a lot to consider, 40,320 alternatives. A shortcut in > such a case becomes the only cut. > > Even the perfect plan, by the way, may not be worth preserving. As the > developers know, > > No battle plan survives first contact with the enemy. > --Helmuth von Moltke > > meaning that any query plan, no matter how fine, is based only on > the state of the data at the time. Change the data enough and it > becomes slow, or useless. > > That's just an outline of the general case. I'll be interested to see > what we know about SQLite's in particular. > > --jkl > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
On 05/19/2014 02:26 PM, Simon Slavin wrote: Are there any advantages to this other than increased speed ? Nope. However I've yet to see anyone complaining that SQLite is too fast and shouldn't be faster :-) I believe that on average an app using SQLite will have a distribution where a few queries are used repeatedly and others are used rarely. A statement cache helps the former and has no real effect on the latter. To me it is telling that all these SQLite wrappers added statement caches. There must have been a performance incentive for so many to do the extra work, including DRH himself for his TCL wrapper. It is very difficult to get statement caches right. They have to be thread safe, and they have to handle multiple statements correctly (eg "select 1; select 2"). They need to have bounded size. They interact with authorizers. They also have to have a copy of the SQL statement. SQLite already has to deal with all these issues, including keeping a copy of the statement so SQLite implementing the cache would save memory. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
On Mon, 19 May 2014 22:26:29 +0100 Simon Slavin wrote: > On 19 May 2014, at 10:21pm, Roger Binns wrote: > > > It seems like most language wrappers for SQLite include some sort of > > statement cache because it is generally useful. It seems like the > > sort of thing that would be helpful within the core of SQLite > > itself, or as an officially supported extra extension. > > Are there any advantages to this other than increased speed ? I'm going to go out on a limb and say No, and moreover it's not obvious there would be any improvement. Do we have statistics on the query planner's performance? How much improvement could the user (reasonably) hope for? I would expect the answer to be Not Much. Query plan caches are leftovers from the days when the ratio of I/O speed to compute speed was much closer, by a few orders of magnitude. On a modern computer I/O dominates everything, including SQL parsing. In theory very complex queries would be the exception, except that query planners long ago developed heuristic shortcuts. Mathematically, an N-way join is a combanatorial problem with N! solutions. When N is, say, 8, that's a lot to consider, 40,320 alternatives. A shortcut in such a case becomes the only cut. Even the perfect plan, by the way, may not be worth preserving. As the developers know, No battle plan survives first contact with the enemy. --Helmuth von Moltke meaning that any query plan, no matter how fine, is based only on the state of the data at the time. Change the data enough and it becomes slow, or useless. That's just an outline of the general case. I'll be interested to see what we know about SQLite's in particular. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
On 19 May 2014, at 10:21pm, Roger Binns wrote: > It seems like most language wrappers for SQLite include some sort of > statement cache because it is generally useful. It seems like the sort of > thing that would be helpful within the core of SQLite itself, or as an > officially supported extra extension. Are there any advantages to this other than increased speed ? (Please don't take the above to be nasty in tone. I don't know the answer, I'm interested, and I have no agenda on this point.) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/05/14 15:27, Richard Hipp wrote: > The TCL interface for SQLite caches the N most recent prepared > statements (where N defaults to 10 but is configurable) and reuses > those prepared statements if the same queries are run again. That > approach seems to work well in practice. It seems like most language wrappers for SQLite include some sort of statement cache because it is generally useful. It seems like the sort of thing that would be helpful within the core of SQLite itself, or as an officially supported extra extension. There is a ticket including pointers to previous mailing list discussion: http://www.sqlite.org/src/tktview?name=ee4b2b48f5 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlN6dfEACgkQmOOfHg372QQmcACfX46ZvXwKt/Y9DnBL0BRxH9QM lFsAoLRh/hjZai4SC5ie/DCpd2+D3NA2 =1Ve9 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
On Sat, May 17, 2014 at 4:55 PM, Baruch Burstein wrote: > What is the overhead of holding open a prepared statement? If my program is > not time critical at all (it is mostly UI bound), but every once in a while > (anywhere from 10 times a second to once every 10 minutes) it needs to run > a few querys, would it make more sense to prepare all of the querys once at > the start of the program (40-50 different querys for the whole program, but > only 2-3 are run at a time), prepare-step-finalize each time as needed, or > (most likely) it doesn't really make a difference in this situation? > > The TCL interface for SQLite caches the N most recent prepared statements (where N defaults to 10 but is configurable) and reuses those prepared statements if the same queries are run again. That approach seems to work well in practice. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
On 17 May 2014, at 9:55pm, Baruch Burstein wrote: > What is the overhead of holding open a prepared statement? If my program is > not time critical at all (it is mostly UI bound), but every once in a while > (anywhere from 10 times a second to once every 10 minutes) it needs to run > a few querys, would it make more sense to prepare all of the querys once at > the start of the program (40-50 different querys for the whole program, but > only 2-3 are run at a time), prepare-step-finalize each time as needed, or > (most likely) it doesn't really make a difference in this situation? No big memory overhead though there is some memory overhead for a prepared query and much more for a query you've executed the first _step() on. No slow-down in any API call just because you have lots of statements prepared. No other reason why you shouldn't do it. If you need to execute the same queries repeatedly it's a reasonable thing to do. You will need to finalize or reset all prepared statements before closing the database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Pre-preparing querys
What is the overhead of holding open a prepared statement? If my program is not time critical at all (it is mostly UI bound), but every once in a while (anywhere from 10 times a second to once every 10 minutes) it needs to run a few querys, would it make more sense to prepare all of the querys once at the start of the program (40-50 different querys for the whole program, but only 2-3 are run at a time), prepare-step-finalize each time as needed, or (most likely) it doesn't really make a difference in this situation? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
