12 <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-7-1-21-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-7-1-21-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>

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

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

    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>