|
1 <?xml version="1.0" encoding="utf-8"?> |
|
2 <!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. --> |
|
3 <!-- This component and the accompanying materials are made available under the terms of the License |
|
4 "Eclipse Public License v1.0" which accompanies this distribution, |
|
5 and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". --> |
|
6 <!-- Initial Contributors: |
|
7 Nokia Corporation - initial contribution. |
|
8 Contributors: |
|
9 --> |
|
10 <!DOCTYPE concept |
|
11 PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
|
12 <concept id="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C" xml:lang="en"><title>SQL Expressions</title><shortdesc>This document explains how the optimizer changes expressions. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
|
13 <section id="GUID-D895B0EA-EC70-4664-BDBB-D49D05AB1360"><title>Introduction</title> <p>The way an expression is written really |
|
14 does matter. When written well expressions not only clearly state what is |
|
15 intended but they can also improve the performance of the SQL engine significantly. |
|
16 This document provides several tips designed to help you improve your SQL |
|
17 expressions. </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-5E95EACE-CC82-55CD-BF88-C8C92A3222E8"><title>Expression |
|
21 Rewriting Optimizations</title> <p>In an earlier section we described how |
|
22 the optimizer would only make use of an index if one of the columns being |
|
23 indexed occurred on one side of certain equality and comparison operators |
|
24 (“<b>=</b> ”, “<b>IN</b> ”, “<b><</b> ”, “<b>></b> ”, “<b><=</b> ”, |
|
25 “<b>>=</b> ”, and sometimes “<b>IS NULL</b> ”). </p> <p>While this is technically |
|
26 true, prior to the stage of the analysis where the optimizer is looking for |
|
27 these kinds of expressions, it may have first modified the <codeph>WHERE</codeph> clause |
|
28 (or the <codeph>ON</codeph> or <codeph>HAVING</codeph> clause) from what was |
|
29 originally entered by the programmer. </p> <p>The next few paragraphs will |
|
30 describe some of these rewriting rules. </p> <p>The query optimizer always |
|
31 rewrites the <codeph>BETWEEN</codeph> operator as a pair of inequalities. |
|
32 So, for example, if the input SQL is this: </p> <codeblock id="GUID-B864D593-8201-5A83-B8CF-55A4A1B8F42F" xml:space="preserve"> |
|
33 SELECT * FROM demo324 WHERE x BETWEEN 7 AND 23; |
|
34 </codeblock> <p>What the query optimizer ultimately sees is this: </p> <codeblock id="GUID-CC868858-4B31-5626-A85D-78816D033E69" xml:space="preserve"> |
|
35 SELECT * FROM demo324 WHERE x>=7 AND x<=23; |
|
36 </codeblock> <p>In this revised form, the optimizer might be able to use an |
|
37 index on the “x” column to speed the operation of the query. </p> <p>Another |
|
38 rewriting rule is that a disjunction of two or more equality tests against |
|
39 the same column is changed into a single IN operator. So if you write: </p> <codeblock id="GUID-AC72BB25-5BC4-520A-A93E-F8BC9D57A437" xml:space="preserve"> |
|
40 SELECT * FROM demo324 WHERE x=7 OR x=23 OR x=47; |
|
41 </codeblock> <p>The WHERE clause will be rewritten into the following form: </p> <codeblock id="GUID-03269149-6080-5039-8817-A2A2A58195D2" xml:space="preserve"> |
|
42 SELECT * FROM demo324 WHERE x IN (7,23,47); |
|
43 </codeblock> <p>The original format was not a candidate for use of indexes. |
|
44 But after the disjunction is converted into a single IN operator the usual |
|
45 index processing logic applies and the query can be made much faster. </p> <p>In |
|
46 order for this rewriting rule to be applied, however, all terms of the disjunction |
|
47 must be equality comparisons against the same column. It will not work to |
|
48 have a disjunction involving two or more columns or involving expressions. |
|
49 So, for instance, the following statements will not be optimized: </p> <codeblock id="GUID-2D2F87A3-BD6F-5849-819F-BA0226E4E215" xml:space="preserve"> |
|
50 SELECT * FROM demo324 WHERE x=7 OR y=23; |
|
51 SELECT * FROM demo324 WHERE x=7 OR +x=23; |
|
52 </codeblock> </section> |
|
53 <section id="GUID-4A75E712-CCF5-568A-894E-2684BA7A9D79"><title> Put Constant |
|
54 Subexpressions inside Subqueries</title> <p>The query parser and compiler |
|
55 in SQLite are designed to be small, fast, and lean. A consequence of this |
|
56 design is that SQLite does not do much in the way of constant folding or common |
|
57 subexpression elimination. SQLite evaluates SQL expressions mostly as written. </p> <p>One |
|
58 way to work around the lack of constant folding in SQLite is to enclose constant |
|
59 subexpressions within a subquery. SQLite does optimize constant subqueries |
|
60 – it evaluates them once, remembers the result, and then reuses that result |
|
61 repeatedly. </p> <p>An example will help clarify how this works. Suppose you |
|
62 have a table that contains a timestamp recorded as the fractional julian day |
|
63 number: </p> <codeblock id="GUID-0A40E590-A590-5AE2-9BC3-9EE627F92207" xml:space="preserve"> |
|
64 CREATE TABLE demo325(tm DATE, data BLOB); |
|
65 </codeblock> <p>A query against this table to find all entries after November |
|
66 8, 2006 might look like the following: </p> <codeblock id="GUID-89C0CA35-B2FC-5217-9E79-52C6C92CEFF8" xml:space="preserve"> |
|
67 SELECT data FROM demo325 WHERE tm>julianday('2006-11-08'); |
|
68 </codeblock> <p>This query works fine. The problem is that the “julianday('2006-11-08')” |
|
69 function gets called repeatedly, once for each row tested, even though the |
|
70 function returns the same value each time. It is much more efficient to call |
|
71 the function one time only and reuse the result over and over. You can accomplish |
|
72 this by moving the function call inside a subquery as follows: </p> <codeblock id="GUID-326760B0-E190-572D-ADD6-03786B6CB4FD" xml:space="preserve"> |
|
73 SELECT data FROM demo325 WHERE tm>(SELECT julianday('2006-11-08')); |
|
74 </codeblock> <p>There are, of course, some cases where multiple evaluations |
|
75 of a function in the WHERE clause is desirable. For example, suppose you want |
|
76 to return roughly one out of every eight records, chosen at random. A suitable |
|
77 query would be: </p> <codeblock id="GUID-1E734B78-91B6-542E-9EDC-52A378944D48" xml:space="preserve"> |
|
78 SELECT data FROM demo325 WHERE (random()&7)==0; |
|
79 </codeblock> <p>In this case, moving the function evaluation into a subquery |
|
80 would not work as desired: </p> <codeblock id="GUID-34B1C0C6-09E7-5994-9DD9-ABB4D5327BA7" xml:space="preserve"> |
|
81 SELECT data FROM demo325 WHERE (SELECT random()&7)==0; |
|
82 </codeblock> <p>In this last example, the result would be either all of the |
|
83 records in the table (probability 12.5%) or none of them (probability 87.5%). |
|
84 The difference here, of course, is that the <codeph>random()</codeph> function |
|
85 is not constant whereas the <codeph>julianday()</codeph> function is. </p> <p>The |
|
86 SQL compiler does not have any way of knowing this so it always assumes the |
|
87 worst: that every function works like <codeph>random()</codeph> and can potentially |
|
88 return a different answer even with the same inputs. Use a subquery if you |
|
89 truly want to make a subexpression constant. </p> </section> |
|
90 </conbody><related-links> |
|
91 <link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> |
|
92 </link> |
|
93 <link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server |
|
94 Guide</linktext></link> |
|
95 <link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext> |
|
96 </link> |
|
97 <link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext> |
|
98 </link> |
|
99 <link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient |
|
100 Tables</linktext></link> |
|
101 <link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile |
|
102 Corruption</linktext></link> |
|
103 <link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index |
|
104 Tips</linktext></link> |
|
105 <link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
|
106 Tips</linktext></link> |
|
107 <link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema |
|
108 Tips</linktext></link> |
|
109 <link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement |
|
110 Tips</linktext></link> |
|
111 <link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext> |
|
112 </link> |
|
113 <link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
|
114 Command</linktext></link> |
|
115 <link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE |
|
116 CLause Tips</linktext></link> |
|
117 </related-links></concept> |