Op 26 jun 2013, om 16:08 heeft Richard Hipp het volgende geschreven:
The next-generation query planner (NGQP) is a rewrite of the query
planner
for SQLite that is faster (reduced run-time for sqlite3_prepare()) and
generates better plans for queries (reducing the run-time for
sqlite3_step()). More information about the NGQP is available here:
http://www.sqlite.org/draft/queryplanner-ng.html
We've run literally millions and millions of test cases on the NGQP
with no
problems. We've tested in on Linux, Windows, Mac, OpenBSD, and
Solaris,
and on 32-bit and 64-bit systems. The Fossil server that manages the
SQLite website is running NGQP. The version of Firefox on which
this email
is being composed is running the NGQP. Everything seems to work
great.
Nevertheless, it is important that you test the NGQP in your
application.
Amalgamations for the latest SQLite containing the NGQP are
available from
the http://www.sqlite.org/draft/download.html page. This should be a
drop-in replacement for the amalgamation you are currently using.
There
are no new APIs or compiler flags to fuss with. Everything works
exactly
as it did before, only a little faster. You should be able to simply
recompile and end up with an application that (hopefully) runs a
little
faster.
Please try this out, and let me know if of your successes and of any
problems you encounter.
--
D. Richard Hipp
d...@sqlite.org
(with regards to the original post of DRH only)
I tested the draft version with a set of query that was extensively
tuned and that admittedly is not a nice target for improvement. Still
in some queries the execution plan is changed favourably (small tables
on top).
Also I noted a favourable change with subqueries, where the optimizer
can do a sort of 'partial' query flattening if a subquery is a join.
Further, if a table that has been analyzed with just a single row, it
is now scanned instead of searched by index. Or is this a cosmetical
change in the output of explain query plan? In another case, with two
rows, the optimizer now opts for a scan on a secundary covering index,
where it searched by primary key before. I can not say much about
performance as these queries are executed in milliseconds and the
timings are fluctuating.
Unfortunately I also found a bug. For some reason the optimizer may
ignore a where clause on an outerjoined table. The example below shows
this, as far as it appears in the execution plan. In my test there was
also a difference in the number of returned rows.
$ sqlite3
SQLite version 3.8.0 2013-06-26 13:22:28
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read ttt130627.sql
create table t (t integer primary key, x, y);
create table t0 (t0 integer primary key, t, z);
explain query plan
select z from t0 left outer join t using (t) where t.t is null;
0|0|0|SCAN TABLE t0
Thanks for the opportunity for feedback, Edzard Pasma
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
