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