DB2 Version 9.7 for Linux, UNIX, and Windows

Efficient SELECT statements

Because SQL is a flexible high-level language, you can write several different SELECT statements to retrieve the same data. However, performance might vary for different forms of the statement, as well as for different optimization classes.

Consider the following guidelines for creating efficient SELECT statements:

Specify only columns that you need. Specifying all columns with an asterisk (*) results in unnecessary processing.
Use predicates that restrict the answer set to only those rows that you need.
When you need significantly fewer than the total number of rows that might be returned, specify the OPTIMIZE FOR clause. This clause affects both the choice of access plan and the number of rows that are blocked in the communication buffer.
To take advantage of row blocking and improve performance, specify the FOR READ ONLY or FOR FETCH ONLY clause. Concurrency improves as well, because exclusive locks are never held on the rows that are retrieved. Additional query rewrites can also occur. Specifying these clauses, as well as the BLOCKING ALL bind option, can similarly improve the performance of queries running against nicknames in a federated database system.
For cursors that will be used with positioned updates, specify the FOR UPDATE OF clause to enable the database manager to choose more appropriate locking levels initially and to avoid potential deadlocks. Note that FOR UPDATE cursors cannot take advantage of row blocking.
For cursors that will be used with searched updates, specify the FOR READ ONLY and the USE AND KEEP UPDATE LOCKS clauses to avoid deadlocks and still allow row blocking by forcing U locks on affected rows.
Avoid numeric data type conversions whenever possible. When comparing values, try to use items that have the same data type. If conversions are necessary, inaccuracies due to limited precision, and performance costs due to runtime conversions might result.
If possible, use the following data types:
- Character instead of varying character for short columns
- Integer instead of float, decimal, or DECFLOAT
- DECFLOAT instead of decimal
- Datetime instead of character
- Numeric instead of character
To decrease the probability that a sort operation will occur, omit clauses such as DISTINCT or ORDER BY if such operations are not required.
To check for the existence of rows in a table, select a single row. Either open a cursor and fetch one row, or perform a single-row SELECT INTO operation. Remember to check for the SQLCODE -811 error if more than one row is found.
Unless you know that the table is very small, do not use the following statement to check for a non-zero value:
```
   select count(*) from <table-name>
```
For large tables, counting all the rows impacts performance.
If update activity is low and tables are large, define indexes on columns that are frequently used in predicates.
Consider using an IN list if the same column appears in multiple predicates. For large IN lists that are used with host variables, looping a subset of the host variables might improve performance.

The following suggestions apply specifically to SELECT statements that access several tables.

Use join predicates to join tables. A join predicate is a comparison between two columns from different tables in a join.
Define indexes on the columns in a join predicate to enable the join to be processed more efficiently. Indexes also benefit UPDATE and DELETE statements containing SELECT statements that access several tables.
If possible, avoid using OR clauses or expressions with join predicates.
In a partitioned database environment, it is recommended that tables being joined are partitioned on the join column.