This section describes the way in which the ADABAS optimizer processes SQL statements. The instructions given in this section and the return messages of the EXPLAIN statement can help to optimize an ADABAS application. It is an important task to find out the optimal SQL statements in an application. This has also decisive influence on the performance of the complete application.
As a tool for optimization, ADABAS provides the EXPLAIN statement. This can be used to easily test the SQL statements within the interactive component QUERY.
The precompilers, moreover, provide the option trace long which, among other things, records the runtime of the SQL statements and provides the possibility of performing an application profile at runtime.
The examples of this section refer to the following declarations:
The way in which an SQL statement may be processed in the shortest possible time with a minimum of storage space depends on:
- the manner of physically storing table rows,
- the size of the tables (number of rows and B* tree pages),
- the definition of the table (of the key columns),
- the indexes,
- the kind of SQL statement (select, insert, update, delete),
- the elements of a select statement (<order clause>, <update clause>, <distinct spec>, for reuse),
- the modifications specified in the update statement,
- the search conditions.
These factors influence the processing of an SQL statement especially when large data sets have to be searched through. This will generally happen with the following SQL statements:
- select; namely as <query statement>, <single select statement>, or <select ordered statement> (not as <select direct statement>),
- insert ... select,
- update ... where <search condition>,
- delete ... where <search condition>.
To optimally process these SQL statements, strategies have to be applied which guarantee that the following objects are obtained:
- Only the rows that are actually needed are searched.
- Temporary result tables are kept as small as possible.
- The search is postponed until the fetch time without generating a result table. (The advantages are: no storage usage for results, fast access to the first results, fast comparison of the obtained results with the desired results).
Not all SQL statements allow the search to be postponed until the fetch time. The following overview contains the corresponding SQL statements:
- select on several tables (join)
- select distinct
- select with for reuse specification
- select with order by specification (in most of the cases).
See also:
Influence of the Search Condition
Ordered Select Statements (single row processing)
Instructions to Increase the Speed
Additional EXPLAIN Information: Columns O, D, T, M
Summary of the Present Chapter