Programming techniques for database performance

 

By changing the coding of your queries, you can improve their performance.

• Use the OPTIMIZE clause
  If an application is not going to retrieve the entire result table for a cursor, using the OPTIMIZE clause can improve performance. The query optimizer modifies the cost estimates to retrieve the subset of rows using the value specified on the OPTIMIZE clause.

• Use FETCH FOR n ROWS
  Applications that perform many FETCH statements in succession may be improved by using FETCH FOR n ROWS. With this clause, you can retrieve multiple rows of data from a table and put them into a host structure array or row storage area with a single FETCH.

• Use INSERT n ROWS
  Applications that perform many INSERT statements in succession may be improved by using INSERT n ROWS. With this clause, you can insert one or more rows of data from a host structure array into a target table. This array must be an array of structures where the elements of the structure correspond to columns in the target table.

• Control database manager blocking
  To improve performance, the SQL runtime attempts to retrieve and insert rows from the database manager a block at a time whenever possible.

• Optimize the number of columns that are selected with SELECT statements
  The number of columns that you specify in the select list of a SELECT statement causes the database manager to retrieve the data from the underlying tables and map the data into host variables in the application programs. By minimizing the number of columns that are specified, processing unit resource usage can be conserved.

• Eliminate redundant validation with SQL PREPARE statements
  The processing which occurs when an SQL PREPARE statement is run is similar to the processing which occurs during precompile processing.

• Page interactively displayed data with REFRESH(*FORWARD)
  In large tables, paging performance is typically degraded because of the REFRESH(*ALWAYS) parameter on the Start SQL (STRSQL) command which dynamically retrieves the latest data directly from the table. Paging performance can be improved by specifying REFRESH(*FORWARD).

 

Parent topic:

Performance and query optimization