General query optimization tips

 

Here are some tips to help your queries run as fast as possible.

• Create indexes whose leftmost key columns match your selection predicates to help supply the optimizer with selectivity values (key range estimates).

• For join queries, create indexes that match your join columns to help the optimizer determine the average number of matching rows.

• Minimize extraneous mapping by specifying only columns of interest on the query. For example, specify only the columns query on the SQL SELECT statement instead of specifying SELECT *. Also, you should specify FOR FETCH ONLY if the columns do not need to be updated.

• If your queries often use table scan access method, use the Reorganize Physical File Member (RGZPFM) command to remove deleted rows from tables or the Change Physical File (CHGPF) REUSEDLT (*YES) command to reuse deleted rows.

Consider using the following options:

• Specify ALWCPYDTA(*OPTIMIZE) to allow the query optimizer to create temporary copies of data so better performance can be obtained. The iSeries™ Access ODBC driver and Query Management driver always uses this mode. If ALWCPYDTA(*YES) is specified, the query optimizer will attempt to implement the query without copies of the data, but may create copies if required. If ALWCPYDTA(*NO) is specified, copies of the data are not allowed. If the query optimizer cannot find a plan that does not use a temporary, then the query cannot be run.

• For SQL, use CLOSQLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) to allow open data paths to remain open for future invocations.

• Specify DLYPRP(*YES) to delay SQL statement validation until an OPEN, EXECUTE, or DESCRIBE statement is run. This option improves performance by eliminating redundant validation.

• Use ALWBLK(*ALLREAD) to allow row blocking for read-only cursors.

 

Parent topic:

Processing queries: Overview

 

Related information

Reorganize Physical File Member (RGZPFM) command
Change Physical File (CHGPF) command