Writing efficient SQL queries

You might be able to rewrite certain SQL statements to enable more efficient access path selection.

Before you begin

Start of changeBefore rewriting existing SQL statements is response to performance problems: Check the organization of the data and the availability and accuracy of the data statistics.End of change

  • Coding SQL statements to avoid unnecessary processing
    By keeping your SQL statements simple, you can limit the amount of processing that they require.
  • Using predicates efficiently
    You can improve how DB2 processes SQL statements by following certain practices when writing predicates.
  • Making predicates eligible for expression-based indexes
    You can create an expression-based index to improve the performance of queries that use column-expression predicates.
  • Using host variables efficiently
    When host variables or parameter markers are used in a query, the actual values are not known when you bind the package or plan that contains the query. DB2 uses a default filter factor to determine the best access path for an SQL statement. If that access path proves to be inefficient, you can do several things to obtain a better access path.
  • Writing efficient subqueries
    A subquery is a SELECT statement within the WHERE or HAVING clause of an INSERT, UPDATE, MERGE, or DELETE SQL statement. By understanding how DB2 processes subqueries, you can estimate the best method to use when writing a given query when several methods can achieve the same result.
  • Query transformations
    DB2 sometimes modifies the text of SQL statements to improve access path efficiency.
  • Materialized query tables and query performance
    One way to improve the performance of dynamic queries that operate on very large amounts of data is to generate the results of all or parts of the queries in advance, and store the results in materialized query tables.
  • Encrypted data and query performance
    Encryption and decryption can degrade the performance of some queries.
  • XML data and query performance
    XML data is more expensive to process, and might affect the performance of most SQL statements. Each row of an XML column contains an XML document, requires more processing, and requires more space in DB2.
  • Using scrollable cursors efficiently
    Scrollable cursors are a valuable tool for writing applications such as screen-based applications, in which the result table is small and you often move back and forth through the data.
  • Efficient queries for tables with data-partitioned secondary indexes
    The number of partitions that DB2 accesses to evaluate a query predicate can affect the performance of the query. A query that provides data retrieval through a data-partitioned secondary index (DPSI) might access some or all partitions of the DPSI.
  • Improving the performance of queries for special situations
    You can use special techniques to improve the access paths of queries for certain particular types of data and certain specific types of applications.
  • Providing more information to DB2 for access path selection
    In certain cases you can improve access path selection for SQL statements by providing more information to DB2.
Parent topic: Programming applications for performance
Related concepts:
Ways to improve query performance
Investigating SQL performance by using EXPLAIN
Related tasks:
Maintaining data organization and statistics
Generating visual representations of access plans (IBM® Data Studio)
Monitoring SQL performance with IBM optimization tools
Modifying catalog statistics to influence access path selection
Related reference:
REORG TABLESPACE
RUNSTATS

Related information

Tuning single SQL statements (IBM Data Studio)