General query optimization tips

• 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 you need to query on the SQL SELECT statement instead of specifying SELECT *. Also, specify FOR FETCH ONLY if the columns do not need to be updated.
• If your queries often use table scan, 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 IBM® i Access ODBC driver and Query Management driver always use this mode. If ALWCPYDTA(*YES) is specified, the query optimizer attempts to implement the query without copies of the data, but might 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.