<?xml version="1.0" encoding="utf-8"?>
<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. -->
<!-- This component and the accompanying materials are made available under the terms of the License 
"Eclipse Public License v1.0" which accompanies this distribution, 
and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". -->
<!-- Initial Contributors:
    Nokia Corporation - initial contribution.
Contributors: 
-->
<!DOCTYPE concept
  PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">
<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">
SELECT x FROM demo326 WHERE y&gt;11.5;
SELECT min(x) FROM demo326 WHERE y&gt;11.5;
</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">
SELECT min(x) FROM demo326;
SELECT max(x) FROm demo326;
</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">
SELECT min(x), max(x) FROM demo326;
</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">
SELECT (SELECT min(x) FROM demo326), (SELECT max(x) FROM dem326);
</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">
SELECT max(x)+1 FROM demo326;
</codeblock> <p>As before, achieve the same result quickly using a subquery: </p> <codeblock id="GUID-8D5BF677-70E0-5475-BE09-D168C106509A" xml:space="preserve">
SELECT (SELECT max(x) FROM demo326)+1;
</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">
SELECT * FROM tableA UNION SELECT * FROM tableB;
SELECT DISTINCT * FROM (SELECT * FROM tableA UNION ALL SELECT * FROM tableB);
</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">
SELECT title FROM album WHERE genre='classical' ORDER BY title LIMIT 5 OFFSET ?
</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">
CREATE INDEX album_idx1 ON album(genre, title);
</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">
SELECT title FROM album WHERE genre='classical' AND title&gt;:bottom
    ORDER BY title ASC LIMIT 5;
</codeblock> <p>And to scroll back up, use this query: </p> <codeblock id="GUID-6D62E7D4-3932-5F0F-88E6-D1BD22FF4822" xml:space="preserve">
SELECT title FROM album WHERE genre='classical' AND title&lt;:top
    ORDER BY title DESC LIMIT 5;
</codeblock> <p>For the scrolling down case the addition of the “title&gt;: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
                Tips</linktext> </link> <link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient
                Tables</linktext> </link> <link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent
                Datafile Corruption</linktext> </link> <link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index
                Tips</linktext> </link> <link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion
                Tips</linktext> </link> <link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema
                Tips</linktext> </link> <link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL
                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
                Command</linktext> </link> <link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE CLause
                Tips</linktext> </link> </related-links></concept>