Introduction to SQL Performance Optimization

Table Definition Optimization

SQL performance is fundamentally dependent upon good data architecture. Dividing your data into multiple tables and establishing relationships between those tables is essential to efficient SQL. How to define tables and their relations to each other is beyond the scope of this book.

This book describes the following operations to optimize a table definition. These operations require that the table be defined, but do not require the table to be populated with data:

• Indices: you can define an index for a table field or group of fields. You can define several different type of indices: standard, bitmap, bitslice, and bitmap extent. SQL optimization uses the defined indices, rather than the data values themselves, to access specific records for a query, update, or delete operation.

• Frozen Plans: you can freeze Data Definition execution plans. (Frozen Plans are also used to freeze query plans).

Table Data Optimization

You can perform the following operations to optimize table access based on analysis of typical data in the table:

• Tune Table: examines typical table data and generates ExtentSize (number of rows), Selectivity (percent of rows having a specific value), and BlockCount metadata. The query optimizer uses this information to determine the most efficient query execution plan.

• Selectivity and Outlier Selectivity: determines the percent of rows having a specific value for a field, and whether one value is an outlier value, a value significantly more common than the other values for that field.

Query Optimization

You can perform the following operations to optimize the execution of a specific query. These query optimizations use existing table definition and table data optimizations:

• Runtime Statistics: used to measure the performance of query execution on your system.

• Show Plan displays the execution plan for a query.

• Cached Queries and Literal Substitution: maintains a cache of recent dynamic queries, allowing for re-execution of a query without repeating the overhead of preparing the query.

• SQL Statements and Frozen Plans allows you to retain a specific query execution plan.

• Index configuration and usage: used to specify how existing indices should be used.

• Index optimization hints: %ALLINDEX, %IGNOREINDEX

• JOIN optimization hints: %FIRSTTABLE, %FULL, %INORDER, %STARTTABLE

• Subquery optimization hints: %NOFLATTEN, %NOMERGE, %NOREDUCE, %NOSVSO

• Parallel query execution: FROM %PARALLEL

• Union optimization: UNION %PARALLEL, UNION/OR

Configuration Optimization

By default, the Memory and Startup Settings default to Automatically configured, and the Maximum Per-Process Memory defaults to 262144 kb. To optimize SQL running on a production system, you should change the default to Manually configured, and increase the Maximum Per-Process Memory setting. For further details, refer to Memory and Startup Settings in the “Configuring Caché” chapter of the Caché System Administration Guide.