On Wed, May 1, 2013 at 11:24 AM, Richard Hipp <d...@sqlite.org> wrote:
> > > On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer < > martin.altma...@googlemail.com> wrote: > >> Hi, >> >> I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. >> > > This seems to be caused by the use of transitive constraints in version > 3.7.16. Your work-around (until an official fix is available in SQLite) is > to put a "+" sign in front of the "elements.id" identifier in the ON > clause: > > SELECT count(*) FROM elements JOIN tags ON +elements.id = > tags.element_id > > WHERE elements.id IN (<list>); > > Thank you for the trouble report. > I think the problem is fixed with http://www.sqlite.org/src/info/faedaeace9 > > > >> Instead of posting the original query, I post a simplified version which >> still experiences the problem with a factor of over 100x: >> >> SELECT COUNT(*) FROM elements JOIN tags ON elements.id = tags.element_id >> WHERE elements.id IN (<list>); >> >> where <list> is large (e.g. 1,2,3,...,2000). In my application this is not >> a contiguous list, so I cannot use BETWEEN. >> >> To demonstrate the issue it suffices that both tables just contain a >> single >> column which is filled with e.g. the integers from 1 to 4000. >> >> CREATE TABLE elements (id INTEGER PRIMARY KEY); >> CREATE TABLE tags (element_id INTEGER); >> >> The running time in 3.7.16.2 increases heavily with the length of <list>, >> which is not the case in 3.7.11. >> As far as I know, indices do not improve the situation (my original >> database has indices). >> Removing the join solves the problem, but in the original query the join >> is >> necessary, because I do not only select COUNT(*). >> >> I tested this on several Linux machines. >> >> Thanks in advance, >> Martin >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
