Runtime Improvement for SQL Applications (SAP Library

Runtime Improvement for SQL Applications

The following are general hints that can contribute to an improved runtime for SQL applications.

When you are building a database, you should derive the definition of the tables from a previously performed examination of the structures. When defining the key columns, you should ensure that the columns that are especially selective, and for which search conditions are specified especially often, are placed at the beginning of the key. This creates the possibility of only having to consider a very small part of the table when processing a SELECT statement.

Only columns with as high a selectivity as possible should be inverted. Do not choose columns such as gender or marital status as single-column index keys, due to their low number of different values. These columns can only be used very rarely for a non-sequential search, as these would usually be more costly than a sequential search.

In the case of relatively static datasets, a large number of columns can be inverted. As long as it is reasonable, you should create multi-column indexes In doing so, you must ensure, as with the definition of the key columns, that columns that are as selective as possible, and that are often used in equality conditions are specified at the beginning of the multi-column index.

You should never invert all of the columns that are used in search conditions. The space for the indexes and the cost for their maintenance is considerable.

If a large number of changes has been made in a table, you should run the UPDATE STATISTICS statement.

You should only formulate search conditions that cannot be fulfilled by all lines. Applications are often written so that the user determines the values of a search condition. If the user does not enter any values, standard values are inserted into the search condition, so that it always returns "true". The database system must now evaluate this ineffective search condition for every line that is to be searched. It is better to execute different SELECT statements depending on a user entry.

You should place the most selective search conditions at the beginning of the condition, as it may be possible to terminate the check earlier.

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 ...