7 Nokia Corporation - initial contribution. |
7 Nokia Corporation - initial contribution. |
8 Contributors: |
8 Contributors: |
9 --> |
9 --> |
10 <!DOCTYPE concept |
10 <!DOCTYPE concept |
11 PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
11 PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
12 <concept xml:lang="en" id="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B"><title>SQL Statement Tips</title><prolog><metadata><keywords/></metadata></prolog><conbody><p>This guide provides tips for using certain SQL statements. </p> <section><title>Introduction</title> <p>There are several techniques for improviding use of certain SQL statements. SQLite includes some special case optimizations for the <codeph>MIN()</codeph> and <codeph>MAX()</codeph> aggregate functions. Where possible, you should use UNION ALL instead of UNION. And you should avoid using OFFSET for scrolling cursors. </p> <p><b>Intended audience:</b> </p> <p>This document is intended to be used by Symbian OS licensees and third party application developers. </p> </section> <section id="GUID-4DFE36ED-1FB2-5EFF-A562-CA4C8A3B8099"><title>The MIN and MAX Optimization</title> <p>SQLite includes some special case optimizations for the <codeph>MIN()</codeph> and <codeph>MAX()</codeph> aggregate functions. The normal way of evaluating a <codeph>MIN()</codeph> or <codeph>MAX()</codeph> aggregate function is to run the entire query, examine every row of the result set, and pick the largest or smallest value. Hence, the following two queries take roughly the same amount of work: </p> <codeblock id="GUID-6CA7EED2-1AE3-5924-80D1-FB4B2CDBCA11" xml:space="preserve"> |
12 <concept id="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B" xml:lang="en"><title>SQL Statement Tips</title><shortdesc>This guide provides tips for using certain SQL statements.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
|
13 <section id="GUID-133D5AA6-B238-4474-B6DF-C63F0451AE0E"><title>Introduction</title> <p>There are several techniques for improving |
|
14 use of certain SQL statements. SQLite includes some special case optimizations |
|
15 for the <codeph>MIN()</codeph> and <codeph>MAX()</codeph> aggregate functions. |
|
16 Where possible, you should use UNION ALL instead of UNION. And you should |
|
17 avoid using OFFSET for scrolling cursors. </p> <p><b>Intended |
|
18 audience:</b> </p> <p>This document is intended to be used by Symbian platform |
|
19 licensees and third party application developers. </p> </section> |
|
20 <section id="GUID-4DFE36ED-1FB2-5EFF-A562-CA4C8A3B8099"><title>The MIN and |
|
21 MAX Optimization</title> <p>SQLite includes some special case optimizations |
|
22 for the <codeph>MIN()</codeph> and <codeph>MAX()</codeph> aggregate functions. |
|
23 The normal way of evaluating a <codeph>MIN()</codeph> or <codeph>MAX()</codeph> aggregate |
|
24 function is to run the entire query, examine every row of the result set, |
|
25 and pick the largest or smallest value. Hence, the following two queries take |
|
26 roughly the same amount of work: </p> <codeblock id="GUID-6CA7EED2-1AE3-5924-80D1-FB4B2CDBCA11" xml:space="preserve"> |
13 SELECT x FROM demo326 WHERE y>11.5; |
27 SELECT x FROM demo326 WHERE y>11.5; |
14 SELECT min(x) FROM demo326 WHERE y>11.5; |
28 SELECT min(x) FROM demo326 WHERE y>11.5; |
15 </codeblock> <p>The only difference in the above two SELECT statements is that the first returns every possible value for “x” whereas the second only returns the smallest. Both require about the same amount of time to run. </p> <p>But there are some special cases where <codeph>MIN()</codeph> and <codeph>MAX()</codeph> are very fast. If the result set of a SELECT consists of only the <codeph>MIN()</codeph> function or the <codeph>MAX()</codeph> function and the argument to that function is an indexed column or the RowID and there is no WHERE or HAVING or GROUP BY clause then the query runs in logarithmic time. So these two queries are very quick: </p> <codeblock id="GUID-D5ECCFCC-E829-5F92-9FC4-2C54D9BEB925" xml:space="preserve"> |
29 </codeblock> <p>The only difference in the above two SELECT statements is |
|
30 that the first returns every possible value for “x” whereas the second only |
|
31 returns the smallest. Both require about the same amount of time to run. </p> <p>But |
|
32 there are some special cases where <codeph>MIN()</codeph> and <codeph>MAX()</codeph> are |
|
33 very fast. If the result set of a SELECT consists of only the <codeph>MIN()</codeph> function |
|
34 or the <codeph>MAX()</codeph> function and the argument to that function is |
|
35 an indexed column or the RowID and there is no WHERE or HAVING or GROUP BY |
|
36 clause then the query runs in logarithmic time. So these two queries are very |
|
37 quick: </p> <codeblock id="GUID-D5ECCFCC-E829-5F92-9FC4-2C54D9BEB925" xml:space="preserve"> |
16 SELECT min(x) FROM demo326; |
38 SELECT min(x) FROM demo326; |
17 SELECT max(x) FROm demo326; |
39 SELECT max(x) FROm demo326; |
18 </codeblock> <p>Note that the result set must contain a single column. The following query is much slower: </p> <codeblock id="GUID-A951F3EF-E950-52E1-847B-F26F4E1ABDEE" xml:space="preserve"> |
40 </codeblock> <p>Note that the result set must contain a single column. The |
|
41 following query is much slower: </p> <codeblock id="GUID-A951F3EF-E950-52E1-847B-F26F4E1ABDEE" xml:space="preserve"> |
19 SELECT min(x), max(x) FROM demo326; |
42 SELECT min(x), max(x) FROM demo326; |
20 </codeblock> <p>If you need the results from this last query quickly, you can rewrite it as follows: </p> <codeblock id="GUID-CC4414E4-A98A-5AF2-82C2-3525939CD26E" xml:space="preserve"> |
43 </codeblock> <p>If you need the results from this last query quickly, you |
|
44 can rewrite it as follows: </p> <codeblock id="GUID-CC4414E4-A98A-5AF2-82C2-3525939CD26E" xml:space="preserve"> |
21 SELECT (SELECT min(x) FROM demo326), (SELECT max(x) FROM dem326); |
45 SELECT (SELECT min(x) FROM demo326), (SELECT max(x) FROM dem326); |
22 </codeblock> <p>In other words, break the query up into two subqueries both of which can be optimized using the MIN/MAX optimization. </p> <p>Note also that the result set must not be an expression on <codeph>MIN()</codeph> or <codeph>MAX()</codeph> - it needs to be a plain <codeph>MIN()</codeph> or <codeph>MAX()</codeph> and nothing else. So the following query is slower: </p> <codeblock id="GUID-9D95BA42-9C6F-5BB3-992F-291126FC7B4B" xml:space="preserve"> |
46 </codeblock> <p>In other words, break the query up into two subqueries both |
|
47 of which can be optimized using the MIN/MAX optimization. </p> <p>Note also |
|
48 that the result set must not be an expression on <codeph>MIN()</codeph> or <codeph>MAX()</codeph> - |
|
49 it needs to be a plain <codeph>MIN()</codeph> or <codeph>MAX()</codeph> and |
|
50 nothing else. So the following query is slower: </p> <codeblock id="GUID-9D95BA42-9C6F-5BB3-992F-291126FC7B4B" xml:space="preserve"> |
23 SELECT max(x)+1 FROM demo326; |
51 SELECT max(x)+1 FROM demo326; |
24 </codeblock> <p>As before, achieve the same result quickly using a subquery: </p> <codeblock id="GUID-8D5BF677-70E0-5475-BE09-D168C106509A" xml:space="preserve"> |
52 </codeblock> <p>As before, achieve the same result quickly using a subquery: </p> <codeblock id="GUID-8D5BF677-70E0-5475-BE09-D168C106509A" xml:space="preserve"> |
25 SELECT (SELECT max(x) FROM demo326)+1; |
53 SELECT (SELECT max(x) FROM demo326)+1; |
26 </codeblock> </section> <section id="GUID-1D3D05CA-4F9F-5AAF-B6D1-D8B6F250BD95"><title>Use UNION ALL in Place of UNION where Possible</title> <p>SQLite, in accordance with the SQL standard, allows two or more SELECT statements to be combined using operators UNION, UNION ALL, INTERSECT, or EXCEPT. The first two, UNION and UNION ALL, are the subject of this section. </p> <p>The UNION and UNION ALL operators do very nearly the same thing, but with one important difference. Both operators return the union of all rows from their left and right queries. The difference is that the UNION operator removes duplicates whereas the UNION ALL operator does not. To look it another way, the following two queries are equivalent: </p> <codeblock id="GUID-3C67BF6E-F3DD-5F3D-B64D-F158A5E3D3F1" xml:space="preserve"> |
54 </codeblock> </section> |
|
55 <section id="GUID-1D3D05CA-4F9F-5AAF-B6D1-D8B6F250BD95"><title>Use UNION ALL |
|
56 in Place of UNION where Possible</title> <p>SQLite, in accordance with the |
|
57 SQL standard, allows two or more SELECT statements to be combined using operators |
|
58 UNION, UNION ALL, INTERSECT, or EXCEPT. The first two, UNION and UNION ALL, |
|
59 are the subject of this section. </p> <p>The UNION and UNION ALL operators |
|
60 do very nearly the same thing, but with one important difference. Both operators |
|
61 return the union of all rows from their left and right queries. The difference |
|
62 is that the UNION operator removes duplicates whereas the UNION ALL operator |
|
63 does not. To look it another way, the following two queries are equivalent: </p> <codeblock id="GUID-3C67BF6E-F3DD-5F3D-B64D-F158A5E3D3F1" xml:space="preserve"> |
27 SELECT * FROM tableA UNION SELECT * FROM tableB; |
64 SELECT * FROM tableA UNION SELECT * FROM tableB; |
28 SELECT DISTINCT * FROM (SELECT * FROM tableA UNION ALL SELECT * FROM tableB); |
65 SELECT DISTINCT * FROM (SELECT * FROM tableA UNION ALL SELECT * FROM tableB); |
29 </codeblock> <p>When you look at it this way, you should clearly see that UNION is just UNION ALL with some extra work to compute the DISTINCT operation. You should also see that UNION ALL is noticeably faster than UNION and uses less temporary storage space too. </p> <p>If you need uniqueness of output values then by all means use UNION. It is there for you and it works. But if you know in advance that your results will be unique or if you do not care, UNION ALL will almost always run faster. </p> </section> <section id="GUID-212C03E7-CC54-5C48-AB9D-B25DC0978513"><title>Avoid Using OFFSET for Scrolling Cursors</title> <p>A common design pattern is to show the results of a large query result in a scrolling window. The query result might contain hundreds or thousands of rows, but only a handful are shown to the user at one time. The user clicks the “Up” or “Down” buttons or drags a scrollbar to move up and down the list. </p> <p>A common example of this is in media players where a user has requested to see all “albums” of a particular “genre”. There might be 200 such albums stored on the device, but the display window is only large enough to show 5 at a time. So the first 5 albums are displayed initially. When the user clicks the Down button the display scrolls down to the next 5 albums. When the user presses the Up button the display scrolls back up to the previous 5 albums. </p> <p>The naïve approach for implementing this behaviour is to keep the index of the topmost album currently displayed. When the user presses Up or Down this index is decremented or incremented by 5 and a query like this is run to refill the display: </p> <codeblock id="GUID-0B46D8A9-213A-58DA-99D7-F46BE4A7FCE5" xml:space="preserve"> |
66 </codeblock> <p>When you look at it this way, you should clearly see that |
|
67 UNION is just UNION ALL with some extra work to compute the DISTINCT operation. |
|
68 You should also see that UNION ALL is noticeably faster than UNION and uses |
|
69 less temporary storage space too. </p> <p>If you need uniqueness of output |
|
70 values then by all means use UNION. It is there for you and it works. But |
|
71 if you know in advance that your results will be unique or if you do not care, |
|
72 UNION ALL will almost always run faster. </p> </section> |
|
73 <section id="GUID-212C03E7-CC54-5C48-AB9D-B25DC0978513"><title>Avoid Using |
|
74 OFFSET for Scrolling Cursors</title> <p>A common design pattern is to show |
|
75 the results of a large query result in a scrolling window. The query result |
|
76 might contain hundreds or thousands of rows, but only a handful are shown |
|
77 to the user at one time. The user clicks the “Up” or “Down” buttons or drags |
|
78 a scrollbar to move up and down the list. </p> <p>A common example of this |
|
79 is in media players where a user has requested to see all “albums” of a particular |
|
80 “genre”. There might be 200 such albums stored on the device, but the display |
|
81 window is only large enough to show 5 at a time. So the first 5 albums are |
|
82 displayed initially. When the user clicks the Down button the display scrolls |
|
83 down to the next 5 albums. When the user presses the Up button the display |
|
84 scrolls back up to the previous 5 albums. </p> <p>The naïve approach for implementing |
|
85 this behavior is to keep the index of the topmost album currently displayed. |
|
86 When the user presses Up or Down this index is decremented or incremented |
|
87 by 5 and a query like this is run to refill the display: </p> <codeblock id="GUID-0B46D8A9-213A-58DA-99D7-F46BE4A7FCE5" xml:space="preserve"> |
30 SELECT title FROM album WHERE genre='classical' ORDER BY title LIMIT 5 OFFSET ? |
88 SELECT title FROM album WHERE genre='classical' ORDER BY title LIMIT 5 OFFSET ? |
31 </codeblock> <p>The bound parameter on the offset field would be filled in with the index of the topmost album to be displayed and the query is run to generate five album titles. Presumably the album table is indexed in such a way that both the WHERE clause and the ORDER BY clause can be satisfied using the index so that no accumulation of results and sorting is required. Perhaps the index looks like this: </p> <codeblock id="GUID-3B67B9DA-DC18-59B1-AFAA-3008A33A8581" xml:space="preserve"> |
89 </codeblock> <p>The bound parameter on the offset field would be filled in |
|
90 with the index of the topmost album to be displayed and the query is run to |
|
91 generate five album titles. Presumably the album table is indexed in such |
|
92 a way that both the WHERE clause and the ORDER BY clause can be satisfied |
|
93 using the index so that no accumulation of results and sorting is required. |
|
94 Perhaps the index looks like this: </p> <codeblock id="GUID-3B67B9DA-DC18-59B1-AFAA-3008A33A8581" xml:space="preserve"> |
32 CREATE INDEX album_idx1 ON album(genre, title); |
95 CREATE INDEX album_idx1 ON album(genre, title); |
33 </codeblock> <p>This approach works find as long as the offset value is small. But the time needed to evaluate this query grows linearly with the offset. So as the user scrolls down towards the bottom of the list, the response time for each click becomes longer and longer. </p> <p>A better approach is to remember the top and bottom title currently being displayed. (The application probably has to do this already in order be able to display the titles). To scroll down, run this query: </p> <codeblock id="GUID-E659EDB4-A3F9-513F-8A46-85C11A2C9DA7" xml:space="preserve"> |
96 </codeblock> <p>This approach works find as long as the offset value is small. |
|
97 But the time needed to evaluate this query grows linearly with the offset. |
|
98 So as the user scrolls down towards the bottom of the list, the response time |
|
99 for each click becomes longer and longer. </p> <p>A better approach is to |
|
100 remember the top and bottom title currently being displayed. (The application |
|
101 probably has to do this already in order be able to display the titles). To |
|
102 scroll down, run this query: </p> <codeblock id="GUID-E659EDB4-A3F9-513F-8A46-85C11A2C9DA7" xml:space="preserve"> |
34 SELECT title FROM album WHERE genre='classical' AND title>:bottom |
103 SELECT title FROM album WHERE genre='classical' AND title>:bottom |
35 ORDER BY title ASC LIMIT 5; |
104 ORDER BY title ASC LIMIT 5; |
36 </codeblock> <p>And to scroll back up, use this query: </p> <codeblock id="GUID-6D62E7D4-3932-5F0F-88E6-D1BD22FF4822" xml:space="preserve"> |
105 </codeblock> <p>And to scroll back up, use this query: </p> <codeblock id="GUID-6D62E7D4-3932-5F0F-88E6-D1BD22FF4822" xml:space="preserve"> |
37 SELECT title FROM album WHERE genre='classical' AND title<:top |
106 SELECT title FROM album WHERE genre='classical' AND title<:top |
38 ORDER BY title DESC LIMIT 5; |
107 ORDER BY title DESC LIMIT 5; |
39 </codeblock> <p>For the scrolling down case the addition of the “title>:bottom” term (where :bottom is a parameter which is bound to the title of the bottom element currently displayed) causes SQLite to jump immediately to the first entry past the current display. There is no longer a need for an OFFSET clause in the query, though we still include “LIMIT 5”. The same index will still work to optimize both the WHERE clause and the ORDER BY clause. </p> <p>The scrolling up case is very similar, although in this case we are looking for titles that are less than the current top element. We have also added the “DESC” tag to the ORDER BY clause so that titles will come out in “descending” order. (The sort order is descending, but the order is ascending if you are talking about the order in which the titles are displayed in the scrolling window). As before, the same album_idx1 index is able handle both terms of the WHERE clause and the descending ORDER BY clause. </p> <p>Both of these queries should be much faster than using OFFSET, especially when the OFFSET is large. OFFSET is convenient for use in ad hoc queries entered on a workstation, but it is rarely helpful in an embedded application. An indexing scheme such as described here is only slightly more complex to implement but is much faster from user's perspective. </p> </section> </conbody><related-links><link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> </link> <link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server Guide</linktext> </link> <link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext> </link> <link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext> </link> <link href="GUID-36DF4CE7-ECDC-5542-A00F-CFFAC307BD6C.dita"><linktext>SQL Memory |
108 </codeblock> <p>For the scrolling down case the addition of the “title>:bottom” |
40 Tips</linktext> </link> <link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient |
109 term (where :bottom is a parameter which is bound to the title of the bottom |
41 Tables</linktext> </link> <link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent |
110 element currently displayed) causes SQLite to jump immediately to the first |
42 Datafile Corruption</linktext> </link> <link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index |
111 entry past the current display. There is no longer a need for an OFFSET clause |
43 Tips</linktext> </link> <link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
112 in the query, though we still include “LIMIT 5”. The same index will still |
44 Tips</linktext> </link> <link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema |
113 work to optimize both the WHERE clause and the ORDER BY clause. </p> <p>The |
45 Tips</linktext> </link> <link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL |
114 scrolling up case is very similar, although in this case we are looking for |
46 Expressions</linktext> </link> <link><linktext/></link><link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext> </link> <link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
115 titles that are less than the current top element. We have also added the |
47 Command</linktext> </link> <link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE CLause |
116 “DESC” tag to the ORDER BY clause so that titles will come out in “descending” |
48 Tips</linktext> </link> </related-links></concept> |
117 order. (The sort order is descending, but the order is ascending if you are |
|
118 talking about the order in which the titles are displayed in the scrolling |
|
119 window). As before, the same album_idx1 index is able handle both terms of |
|
120 the WHERE clause and the descending ORDER BY clause. </p> <p>Both of these |
|
121 queries should be much faster than using OFFSET, especially when the OFFSET |
|
122 is large. OFFSET is convenient for use in ad hoc queries entered on a workstation, |
|
123 but it is rarely helpful in an embedded application. An indexing scheme such |
|
124 as described here is only slightly more complex to implement but is much faster |
|
125 from user's perspective. </p> </section> |
|
126 </conbody><related-links> |
|
127 <link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> |
|
128 </link> |
|
129 <link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server |
|
130 Guide</linktext></link> |
|
131 <link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext> |
|
132 </link> |
|
133 <link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext> |
|
134 </link> |
|
135 <link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient |
|
136 Tables</linktext></link> |
|
137 <link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile |
|
138 Corruption</linktext></link> |
|
139 <link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index |
|
140 Tips</linktext></link> |
|
141 <link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
|
142 Tips</linktext></link> |
|
143 <link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema |
|
144 Tips</linktext></link> |
|
145 <link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext> |
|
146 </link> |
|
147 <link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext> |
|
148 </link> |
|
149 <link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
|
150 Command</linktext></link> |
|
151 <link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE |
|
152 CLause Tips</linktext></link> |
|
153 </related-links></concept> |