IBM Books

Administration Guide

Tuning Queries

This section provides specific considerations and guidelines to help you fine-tune the SQL statements in an application program. As a general rule, these guidelines may help design a program that minimizes the use of system resources and the amount of time needed to access data in a very large table. Depending on the amount of optimization that takes place when the SQL statement is compiled, you may not need to fine-tune your SQL statements. The SQL compiler can rewrite your SQL into more efficient forms. See "Query Rewrite by the SQL Compiler" and "Adjusting the Optimization Class".

It is also important to note that the access plan chosen by the optimizer is also affected by other factors, including environmental considerations and system catalog statistics. If you conduct benchmark testing of the performance of your applications, you can make adjustments that can improve the access plan.

Using a select-statement

The SQL language is a high-level language with much flexibility. As a result, different select-statements can be written to retrieve the same data. However, the performance can vary for the different forms and the different classes of optimization.

It is important to note the SQL compiler (including the query rewrite and optimization phases) must choose an access plan that will produce the result set for the query you have coded. Therefore, as noted in many of the following guidelines, you should code your query to obtain only the data that you need. This ensures that the SQL compiler can choose the best access plan for your needs.

The guidelines for using a select-statement are:

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

For more information see "Join Concepts".

Also, refer to the Embedded SQL Programming Guide for more information on coding SQL statements with joins and subqueries.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]