On Mon, May 19, 2014 at 8:30 PM, James K. Lowden
<jklow...@schemamania.org>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,
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
