<?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 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>
<section id="GUID-133D5AA6-B238-4474-B6DF-C63F0451AE0E"><title>Introduction</title> <p>There are several techniques for improving
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 platform
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 behavior 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-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 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>