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
