This documentation describes how the Optimizer processes SQL statements. It provides information about the meaning of optimal SQL statements and options for creating them for your application.
For general information about the database system, see Database Administration.
For information about the syntax of SQL statements that can be used in the database system, see the Reference Manual.
Determining the optimal SQL statements for a database application is an important task. Optimal SQL statements significantly influence the performance of the entire database application. An SQL statement is optimal if it is written in such a way that it can be processed in as short a time as possible, while requiring a minimum amount of main memory.
To process SQL statements optimally, the factors that influence the processing of an SQL statement must be considered. In particular, you should consider the influence of the search conditions on the processing of an SQL statement. There are several other options for speeding up the processing of SQL statements.
The SQL statements should be written in such a way that search strategies can be used. The following goals should be achieved in doing so:
· Search only those rows that it is necessary to search
· Restrict results tables and temporary results tables to the smallest possible size
· Postponement of the Search to the FETCH Time
If a large number of changes has been made in a table, you should run the UPDATE STATISTICS statement. This statement updates the details about the tables that are necessary for the work of the Optimizer (such as value distribution, size of tables, and inversions).
If you consider all of these hints, you can write optimal SQL statements.
...
1. Check your SQL statements for correctness, for example, with the help of the tool SQL Studio.
2. Analyze your SQL statements with regard to the performance that can be achieved through them. To do this, use the appropriate EXPLAIN statements.
Only after you have completed these checks of your SQL statements should you insert them into your application program.