On 05/20/2014 07:30 AM, James K. Lowden wrote:
On Mon, 19 May 2014 22:26:29 +0100
Simon Slavin <slav...@bigfraud.org> wrote:
On 19 May 2014, at 10:21pm, Roger Binns <rog...@rogerbinns.com> 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
