|
1 <?xml version="1.0" encoding="utf-8"?> |
|
2 <!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. --> |
|
3 <!-- This component and the accompanying materials are made available under the terms of the License |
|
4 "Eclipse Public License v1.0" which accompanies this distribution, |
|
5 and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". --> |
|
6 <!-- Initial Contributors: |
|
7 Nokia Corporation - initial contribution. |
|
8 Contributors: |
|
9 --> |
|
10 <!DOCTYPE concept |
|
11 PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
|
12 <concept id="GUID-0D093559-793F-5CDC-BB66-8FE1C8A3850E" xml:lang="en"><title>SQL |
|
13 Optimization Guide</title><shortdesc>This document discusses SQL optimization techniques.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
|
14 <p>Several best practice tips have been created as a response to customer |
|
15 experience feedback. Use this guide to decide where to start optimizing. </p> |
|
16 <section id="GUID-4AF79E32-4BC9-5A13-BF0E-F7F329982F1C"><title> Optimization |
|
17 issues</title> <p>SQL performance can be optimized by following good practice |
|
18 in the design of databases, formulation of SQL queries, use of indexes and |
|
19 other techniques. </p> <p id="GUID-828B8B44-8CC3-5CCA-A4C9-BF90353B2002"><b> Transient tables</b> </p> <p>Transient |
|
20 tables are created by the database engine to compute intermediate results. </p> <p>Transient |
|
21 tables are stored on disk by default, which may result in unexpected I/O calls |
|
22 that will probably have a negative impact on database performance. </p> <p id="GUID-0F43181C-2C32-5B5D-8AA2-4588D3DAF171-GENID-1-8-1-21-1-1-6-1-1-9-1-7-1-5-1-4-1-3-2-6"><b>Prevent datafile corruption</b> </p> <p>Datafile |
|
23 corruption is corruption of the file containing a database with invalid data. </p> <p>Datafile |
|
24 corruption can occur after a system crash or loss of power. SQLite maintains |
|
25 files called rollback journals from which the last valid state of a database |
|
26 can be reconstructed. However, if a rollback journal is removed or renamed |
|
27 during recovery from a crash, for instance in the course of application recovery |
|
28 the database will remain corrupted. </p> <p id="GUID-0F43181C-2C32-5B5D-8AA2-4588D3DAF171-GENID-1-8-1-21-1-1-6-1-1-9-1-7-1-5-1-4-1-3-2-9"><b>SQL index tips</b> </p> <p>Indexes |
|
29 are added either manually or automatically to columns of a data table to speed |
|
30 up SELECT operations. </p> <p>Indexes greatly speed up the performance of |
|
31 SELECT statements but can impact on insertions, updates and deletions and |
|
32 memory usage. It is not efficient to index all columns regardless of their |
|
33 purpose and under certain circumstances indexes decrease performance. They |
|
34 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 |
|
35 fast operation but it can execute slowly if each individual operation is wrapped |
|
36 in a transaction, which is the default behavior. </p> <p>Multiple insertion |
|
37 statements should be grouped into transactions by explicit use of the COMMIT |
|
38 statement or else by use of TEMP tables which can be inserted into the database |
|
39 as a batch in a single operation, and by reuse of prepared statements. Insert |
|
40 statements should use bound parameters for reasons of efficiency and to prevent |
|
41 SQL injection attacks. </p> <p><b>SQL |
|
42 schema tips</b> </p> <p>A database schema is a definition of its structure |
|
43 including the tables, data types and keys. </p> <p>The design of a database |
|
44 can impact on the efficiency of the SQL queries run on it. Correct ordering |
|
45 of columns in a table and use of keys makes a database more efficient, while |
|
46 it is inefficient to store numerous large data structures such as BLOBs directly |
|
47 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 |
|
48 which uses operators to combine values to form new values. </p> <p>Some ways |
|
49 of writing an expression are more efficient than others because of the way |
|
50 the SQLite optimizer rewrites operators. It is also important to use constant |
|
51 subexpressions within subqueries. </p> <p><b>SQL |
|
52 statement tips</b> </p> <p>Certain SQL statements have special case optimizations. </p> <p>There |
|
53 are special rules for using the MIN and MAX functions, the UNION and UNION |
|
54 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 |
|
55 virtual tables. </p> <p>A join on multiple tables involves reordering their |
|
56 columns to construct the combined virtual table. The optimizer attempts to |
|
57 perform this ordering in the most efficient way but is not always successful. |
|
58 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 |
|
59 about the distribution of values in a populated database. </p> <p>The ANALYZE |
|
60 command creates a table SQLITE_STAT1 containing the statistical profile of |
|
61 the database at the time when it was run. The optimizer uses that data to |
|
62 choose between implementation options. Developers can also use the data to |
|
63 make their own optimizations and they can modify the table to influence the |
|
64 behavior of the optimizer. </p> <p><b>SQL |
|
65 WHERE clause tips</b> </p> <p>The SQL WHERE clause restricts the effect of |
|
66 an SQL statement to rows satisfying the expression which it contains. </p> <p>The |
|
67 performance of a WHERE clause can be improved by following certain rules. |
|
68 It is inefficient to use expressions within WHERE clauses instead of indexed |
|
69 column names. Also, the AND operator should always be preferred to the OR |
|
70 operator. </p> </section> |
|
71 </conbody><related-links> |
|
72 <link href="GUID-831F9AA3-0209-53DC-96C4-04157F22870C.dita"><linktext>SQL Developer |
|
73 Tips</linktext></link> |
|
74 </related-links></concept> |