All the second program does is - start a transaction - lock two tables - halt
Which is quite fast ;) The first one goes through a lot of trouble before realizing it has already finished. -----Ursprüngliche Nachricht----- Von: Nissl Reinhard [mailto:reinhard.ni...@fee.de] Gesendet: Dienstag, 08. Juli 2014 14:58 An: General Discussion of SQLite Database Betreff: Re: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a "limit 0" clause Hi Gunter, I'll provide explanations for the first and the last of the previously mentioned queries: sele order from deta ---- ------------- ---- ---- 1 0 0 SCAN TABLE ProfileDetail AS d USING COVERING INDEX ix_profiledetail_client 0 0 1 SCAN SUBQUERY 1 AS p 0 1 0 SEARCH TABLE QueryTrace AS q USING INDEX ix_querytrace_sumid_procid (SummaryId=? AND ProcessId=?) 0 0 0 USE TEMP B-TREE FOR ORDER BY sele order from deta ---- ------------- ---- ---- 2 0 0 SCAN TABLE ProfileDetail AS d USING COVERING INDEX ix_profiledetail_client 1 0 1 SCAN SUBQUERY 2 AS p 1 1 0 SEARCH TABLE QueryTrace AS q USING INDEX ix_querytrace_sumid_procid (SummaryId=? AND ProcessId=?) 1 0 0 USE TEMP B-TREE FOR ORDER BY 0 0 0 SCAN SUBQUERY 1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 76 0 00 1 Integer 21 1 0 00 2 Once 0 21 0 00 3 OpenEphemeral 2 3 0 00 4 Null 1 7 0 00 5 OpenRead 5 28963 0 k(4,nil,nil,nil,nil) 00 6 Rewind 5 20 2 0 00 7 Column 5 2 2 00 8 Eq 3 19 2 (BINARY) 69 9 Column 5 0 4 00 10 Column 5 1 5 00 11 SCopy 2 6 0 00 12 Ne 4 15 7 (BINARY) 80 13 Ne 5 15 8 (BINARY) 80 14 Eq 6 19 9 (BINARY) 80 15 Copy 4 7 2 00 16 MakeRecord 4 3 10 00 17 NewRowid 2 11 0 00 18 Insert 2 10 11 08 19 Next 5 7 0 01 20 Close 5 0 0 00 21 Return 1 0 0 00 22 OpenEphemeral 6 3 0 k(1,-B) 00 23 Integer 0 12 0 00 24 Goto 0 75 0 00 25 OpenRead 1 9 0 8 00 26 OpenRead 7 5 0 k(3,nil,nil,nil) 00 27 Rewind 2 59 0 00 28 Column 2 0 13 00 29 IsNull 13 58 0 00 30 Column 2 1 14 00 31 IsNull 14 58 0 00 32 Affinity 13 2 0 dd 00 33 SeekGE 7 58 13 2 00 34 IdxGT 7 58 13 2 00 35 IdxRowid 7 15 0 00 36 Seek 1 15 0 00 37 Copy 15 16 0 00 38 Column 7 0 17 00 39 Column 1 2 18 00 40 Column 1 3 19 00 41 Column 7 1 20 00 42 Column 1 5 21 00 43 Column 1 6 22 00 44 Column 1 7 23 00 45 Column 2 2 24 00 46 MakeRecord 16 9 25 00 47 Column 1 6 27 00 48 Sequence 6 28 0 00 49 Move 25 29 1 00 50 MakeRecord 27 3 26 00 51 IdxInsert 6 26 0 00 52 IfZero 12 55 0 00 53 AddImm 12 -1 0 00 54 Goto 0 57 0 00 55 Last 6 0 0 00 56 Delete 6 0 0 00 57 Next 7 34 1 00 58 Next 2 28 0 01 59 Close 1 0 0 00 60 Close 7 0 0 00 61 OpenPseudo 8 25 9 00 62 Sort 6 75 0 00 63 Column 6 2 25 00 64 Column 8 0 16 20 65 Column 8 1 17 00 66 Column 8 2 18 00 67 Column 8 3 19 00 68 Column 8 4 20 00 69 Column 8 5 21 00 70 Column 8 6 22 00 71 Column 8 7 23 00 72 Column 8 8 24 00 73 ResultRow 16 9 0 00 74 Next 6 63 0 00 75 Halt 0 0 0 00 76 Transaction 0 0 28 0 01 77 TableLock 0 4 0 ProfileDetail 00 78 TableLock 0 9 0 QueryTrace 00 79 String8 0 3 0 unknown 00 80 Goto 0 1 0 00 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 94 0 00 1 InitCoroutine 1 77 2 00 2 Integer 22 2 0 00 3 Once 0 22 0 00 4 OpenEphemeral 3 3 0 00 5 Null 1 8 0 00 6 OpenRead 6 28963 0 k(4,nil,nil,nil,nil) 00 7 Rewind 6 21 3 0 00 8 Column 6 2 3 00 9 Eq 4 20 3 (BINARY) 69 10 Column 6 0 5 00 11 Column 6 1 6 00 12 SCopy 3 7 0 00 13 Ne 5 16 8 (BINARY) 80 14 Ne 6 16 9 (BINARY) 80 15 Eq 7 20 10 (BINARY) 80 16 Copy 5 8 2 00 17 MakeRecord 5 3 11 00 18 NewRowid 3 12 0 00 19 Insert 3 11 12 08 20 Next 6 8 0 01 21 Close 6 0 0 00 22 Return 2 0 0 00 23 OpenEphemeral 7 3 0 k(1,-B) 00 24 Integer 0 13 0 00 25 Goto 0 76 0 00 26 OpenRead 2 9 0 8 00 27 OpenRead 8 5 0 k(3,nil,nil,nil) 00 28 Rewind 3 60 0 00 29 Column 3 0 14 00 30 IsNull 14 59 0 00 31 Column 3 1 15 00 32 IsNull 15 59 0 00 33 Affinity 14 2 0 dd 00 34 SeekGE 8 59 14 2 00 35 IdxGT 8 59 14 2 00 36 IdxRowid 8 16 0 00 37 Seek 2 16 0 00 38 Copy 16 17 0 00 39 Column 8 0 18 00 40 Column 2 2 19 00 41 Column 2 3 20 00 42 Column 8 1 21 00 43 Column 2 5 22 00 44 Column 2 6 23 00 45 Column 2 7 24 00 46 Column 3 2 25 00 47 MakeRecord 17 9 26 00 48 Column 2 6 28 00 49 Sequence 7 29 0 00 50 Move 26 30 1 00 51 MakeRecord 28 3 27 00 52 IdxInsert 7 27 0 00 53 IfZero 13 56 0 00 54 AddImm 13 -1 0 00 55 Goto 0 58 0 00 56 Last 7 0 0 00 57 Delete 7 0 0 00 58 Next 8 35 1 00 59 Next 3 29 0 01 60 Close 2 0 0 00 61 Close 8 0 0 00 62 OpenPseudo 9 26 9 00 63 Sort 7 76 0 00 64 Column 7 2 26 00 65 Column 9 0 17 20 66 Column 9 1 18 00 67 Column 9 2 19 00 68 Column 9 3 20 00 69 Column 9 4 21 00 70 Column 9 5 22 00 71 Column 9 6 23 00 72 Column 9 7 24 00 73 Column 9 8 25 00 74 Yield 1 0 0 00 75 Next 7 64 0 00 76 EndCoroutine 1 0 0 00 77 Integer 0 31 0 00 78 Goto 0 93 0 00 79 InitCoroutine 1 0 2 00 80 Yield 1 93 0 00 81 Copy 17 32 0 00 82 Copy 18 33 0 00 83 Copy 19 34 0 00 84 Copy 20 35 0 00 85 Copy 21 36 0 00 86 Copy 22 37 0 00 87 Copy 23 38 0 00 88 Copy 24 39 0 00 89 Copy 25 40 0 00 90 ResultRow 32 9 0 00 91 IfZero 31 93 -1 00 92 Goto 0 80 0 00 93 Halt 0 0 0 00 94 Transaction 0 0 28 0 01 95 TableLock 0 4 0 ProfileDetail 00 96 TableLock 0 9 0 QueryTrace 00 97 String8 0 4 0 unknown 00 98 Goto 0 1 0 00 Bye. -- Reinhard Nißl, TB3, -198 -----Ursprüngliche Nachricht----- Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Im Auftrag von Hick Gunter Gesendet: Dienstag, 8. Juli 2014 11:58 An: General Discussion of SQLite Database Betreff: Re: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a "limit 0" clause 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
