Re: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a "limit 0" clause

[Hick Gunter]

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