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.
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
