<?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 xml:lang="en" id="GUID-0D093559-793F-5CDC-BB66-8FE1C8A3850E"><title>SQL Optimisation Guide</title><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> Optimisation issues</title> <p>SQL performance can be optimised 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-6-1-20-1-1-6-1-1-10-1-7-1-5-1-3-1-2-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-6-1-20-1-1-6-1-1-10-1-7-1-5-1-3-1-2-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 behaviour. </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 optimiser 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 optimisations. </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 optimiser attempts to perform this ordering in the most efficient way but is not always successful. You can override the optimiser 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 optimiser uses that data to choose between implementation options. Developers can also use the data to make their own optimisations and they can modify the table to influence the behaviour of the optimiser. </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>