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.
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
