<?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-0D093559-793F-5CDC-BB66-8FE1C8A3850E" xml:lang="en"><title>SQL
Optimization Guide</title><shortdesc>This document discusses SQL optimization techniques.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>
<p>Several best practice tips have been created as a response to customer
experience feedback. Use this guide to decide where to start optimizing. </p>
<section id="GUID-4AF79E32-4BC9-5A13-BF0E-F7F329982F1C"><title> Optimization
issues</title> <p>SQL performance can be optimized by following good practice
in the design of databases, formulation of SQL queries, use of indexes and
other techniques. </p> <p id="GUID-828B8B44-8CC3-5CCA-A4C9-BF90353B2002"><b> Transient tables</b> </p> <p>Transient
tables are created by the database engine to compute intermediate results. </p> <p>Transient
tables are stored on disk by default, which may result in unexpected I/O calls
that will probably have a negative impact on database performance. </p> <p id="GUID-0F43181C-2C32-5B5D-8AA2-4588D3DAF171-GENID-1-10-1-22-1-1-6-1-1-9-1-7-1-5-1-4-1-3-2-6"><b>Prevent datafile corruption</b> </p> <p>Datafile
corruption is corruption of the file containing a database with invalid data. </p> <p>Datafile
corruption can occur after a system crash or loss of power. SQLite maintains
files called rollback journals from which the last valid state of a database
can be reconstructed. However, if a rollback journal is removed or renamed
during recovery from a crash, for instance in the course of application recovery
the database will remain corrupted. </p> <p id="GUID-0F43181C-2C32-5B5D-8AA2-4588D3DAF171-GENID-1-10-1-22-1-1-6-1-1-9-1-7-1-5-1-4-1-3-2-9"><b>SQL index tips</b> </p> <p>Indexes
are added either manually or automatically to columns of a data table to speed
up SELECT operations. </p> <p>Indexes greatly speed up the performance of
SELECT statements but can impact on insertions, updates and deletions and
memory usage. It is not efficient to index all columns regardless of their
purpose and under certain circumstances indexes decrease performance. They
are best added to a database at the last stage of development. </p> <p><b>SQL insertion tips</b> </p> <p>INSERT, like UPDATE and DELETE, is an inherently
fast operation but it can execute slowly if each individual operation is wrapped
in a transaction, which is the default behavior. </p> <p>Multiple insertion
statements should be grouped into transactions by explicit use of the COMMIT
statement or else by use of TEMP tables which can be inserted into the database
as a batch in a single operation, and by reuse of prepared statements. Insert
statements should use bound parameters for reasons of efficiency and to prevent
SQL injection attacks. </p> <p><b>SQL
schema tips</b> </p> <p>A database schema is a definition of its structure
including the tables, data types and keys. </p> <p>The design of a database
can impact on the efficiency of the SQL queries run on it. Correct ordering
of columns in a table and use of keys makes a database more efficient, while
it is inefficient to store numerous large data structures such as BLOBs directly
in a database. Schemas are best kept small. </p> <p><b>SQL expressions</b> </p> <p>An expression is the part of an SQL statement
which uses operators to combine values to form new values. </p> <p>Some ways
of writing an expression are more efficient than others because of the way
the SQLite optimizer rewrites operators. It is also important to use constant
subexpressions within subqueries. </p> <p><b>SQL
statement tips</b> </p> <p>Certain SQL statements have special case optimizations. </p> <p>There
are special rules for using the MIN and MAX functions, the UNION and UNION
ALL operators and OFFSET clauses efficiently within SELECT statements. </p> <p><b>SQL joins</b> </p> <p>SQL joins are used to combine multiple tables into
virtual tables. </p> <p>A join on multiple tables involves reordering their
columns to construct the combined virtual table. The optimizer attempts to
perform this ordering in the most efficient way but is not always successful.
You can override the optimizer by using the CROSS JOIN operator. </p> <p><b>ANALYZE command</b> </p> <p>The ANALYZE command is used to provide statistics
about the distribution of values in a populated database. </p> <p>The ANALYZE
command creates a table SQLITE_STAT1 containing the statistical profile of
the database at the time when it was run. The optimizer uses that data to
choose between implementation options. Developers can also use the data to
make their own optimizations and they can modify the table to influence the
behavior of the optimizer. </p> <p><b>SQL
WHERE clause tips</b> </p> <p>The SQL WHERE clause restricts the effect of
an SQL statement to rows satisfying the expression which it contains. </p> <p>The
performance of a WHERE clause can be improved by following certain rules.
It is inefficient to use expressions within WHERE clauses instead of indexed
column names. Also, the AND operator should always be preferred to the OR
operator. </p> </section>
</conbody><related-links>
<link href="GUID-831F9AA3-0209-53DC-96C4-04157F22870C.dita"><linktext>SQL Developer
Tips</linktext></link>
</related-links></concept>