Output from .explain explain query plan select... explain select...
would be interesting -----Ursprüngliche Nachricht----- Von: Nissl Reinhard [mailto:reinhard.ni...@fee.de] Gesendet: Dienstag, 08. Juli 2014 11:46 An: sqlite-users@sqlite.org Betreff: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a "limit 0" clause Hi, when I run this query: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client <> 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC ) limit 0 it takes quite a while to return no result (no result is expected due to the "limit 0" clause). So I thought, the query optimizer isn't smart enough and moved the limit 0 clause to the inner statement for testing: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client <> 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC limit 0 ) This query still takes a while to execute. By mistake, I had accidentally also tried the query with two "limit 0" clauses like this: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client <> 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC limit 0 ) limit 0 This query runs by magnitudes faster than the former ones. Inspired by this behavior, I've tried the following query, which also has two "limit 0" clauses, but unmodified inner statement (this is a constraint in my use case): select * from ( select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client <> 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC ) limit 0 ) limit 0 This query is also fast. While it seems that I now have a workaround for my use case, it would be nice if a single "limit 0" clause at the outer statement would be sufficient for a fast query. Bye. -- Reinhard Nißl, TB3, -198 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ----------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users