<?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>11.5;

SELECT min(x) FROM demo326 WHERE y>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>: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<: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>