This section contains instructions which help to improve the runtime of applications.
- If a database is built, the definition of the tables should be derived from the structures previously investigated. When defining the key columns, it should be ensured that the most selective columns to which conditions are most frequently applied are placed at the beginning of the key. This guarantees that only a very small part of the table has to be considered during the processing of the select.
- Only columns of high selectivity should be indexed. No single-column index should be created on columns such as sex or personal status because of the small number of distinct values. These columns could be used very seldom for a non-sequential strategy, because this would normally be more costly than the sequential search.
- For relatively static data sets, many columns can be indexed. If it makes sense, multiple indexes should be created. As for the key column definition, it should be ensured that the most selective columns which are frequently used in EQUAL conditions are specified at the beginning of the multiple index.
- Not all the columns which are used in conditions should be indexed. The space required for the indexes and the overhead for their maintenance would be considerable.
- If many updates have been made to a table, UPDATE STATISTICS should be performed.
- ADABAS implicitly performs UPDATE STATISTICS when it determines that a table has been modified to a certain extent. ADABAS, however, is not able to recognize any change relevant to the correct determination of the currently best strategy and to execute an implicit UPDATE STATISTICS.
- Only conditions which are not met by all rows should be formulated. Frequently, applications are built in which the user defines the values of a condition. If the user does not specify any values, default values are entered into the condition so that it always yields 'true'. The database system must then evaluate such an inefficient condition for every row to be checked. It is better to issue various select statements which depend on user input.
- The most selective conditions should be placed at the beginning of the search condition.
- The specification
columnx between 1 and 5
is better than
columnx in (1,2,3,4,5)
- The specification
columnx in (1,13,24,...)
is better than
columnx=1 or columnx=13 or columnx=24 or ...