Hi all,
I'm investigating some apparent slowdowns of an app when upgrading to
more recent versions of SQLite3. This has been a problem for quite a
while, so I had to go back pretty far to find a version of SQLite3 where
things were performing as desired. For the moment, I've just compared
3.4.2 to 3.6.16 (I also checked 3.5.9 checked out from Fossil and I
think it exhibited the same behavior as 3.6.16).
The basic form of the query which I'm having problems with is this:
CREATE TABLE
datathunkwithindex ([a] INTEGER);
CREATE INDEX
idx_datathunkwithindex_a
ON
datathunkwithindex([a]);
SELECT DISTINCT
datathunkwithindex.oid, datathunkwithindex.[a]
FROM datathunkwithindex
WHERE
((datathunkwithindex.[a] = ?) AND (datathunkwithindex.oid > ?))
ORDER BY
datathunkwithindex.[a] ASC,
datathunkwithindex.oid ASC
LIMIT
2;
Though the "DISTINCT" is apparently useless here, as oids must be
distinct, there are other more complicated queries of a similar form
which do need it and which exhibit the same performance problem.
The app has some rudimentary tests for performance which pointed at this
statement as the cause of the slowdown. The tests use the tracing hooks
to count how many operations are executed to satisfy a query. If more
operations are required as more rows are added to the relevant tables,
the test fails.
This led me to look at the vdbe for the query in each version, and
indeed the 3.4.2 version is shorter and appears to make better use of
indexes (but I wouldn't say I'm a master of reading vdbe). EXPLAIN
output for each version attached (3.6.16 version includes the actual
name of the table -
"item_xmantissa_test_test_scrolltable_datathunkwithindex_v1" which
corresponds to the shortened "datathunkwithindex" above).
Is this a performance regression? Is the way I'm testing for
performance regressions bogus?
Thanks,
Jean-Paul
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
