Mercurial Mercurial > oss > FCL > sftools > depl > docscontent / diff
summary | shortlog | changelog | graph | tags | bookmarks | branches | files | changeset | file | latest | revisions | annotate | diff | comparison | raw | help
Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Symbian3/SDK/Source/GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita
changeset 7 51a74ef9ed63 
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Symbian3/SDK/Source/GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita	Wed Mar 31 11:11:55 2010 +0100
@@ -0,0 +1,153 @@
+<?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>
\ No newline at end of file
FCL/sftools/depl/docscontent