Performance

The performance of a database instance largely depends on the configuration of the database instance, the database design and the formulation of the individual SQL statements.

Search Conditions

Search conditions describe the search area of an SQL statement. Search conditions are Boolean expressions (terms) that can be linked using AND or OR. AND conditions reduce the search area, OR conditions enlarge it.

SELECT firstname, name, zip FROM hotel.customer
  WHERE (title ='Company') AND (name > 'Randolph')

In order to fulfill a search condition, the database system can always search the entire table (TABLE SCAN). This is, however, usually not the most efficient method. The more you restrict the search area, the quicker the database system can search through the data records that qualify for the search condition.

For this reason it is also advantageous if the database system can search a table area whose data records are sorted. This is what the keys of a table are for. Each table has a primary key. You can also define secondary keys (indexes), which you can create for one or more columns.

To process SQL statements as quickly as possible, the database system uses the following procedures, among others:

?      It transforms SQL statements into the form best suited to the search.

See Automatic Transformation of SQL Statements

?      It calculates the best search strategy for each SQL statement.

See SQL Optimizer

See also:

Search Conditions, Search Strategies

SQL Reference Manual, Search Condition (search_condition)

Recommendations

?      To achieve good I/O performance, observe the notes in Planning a Database.

?      When defining the data, pay attention to the database design tips.

?      Regularly update the SQL Optimizer Statistics, in particular after a lot of data has been changed.

?      For indexes, use columns that are as selective as possible (columns that have many different values).

?      Formulate your search conditions to be as selective as possible.

Formulate only search conditions that are not fulfilled by all rows: Oftentimes when users can choose the values for a search condition, they do not enter any values at all. The database system must then evaluate the ineffectual search condition for every row included in the search. It is better to execute different SELECT statements depending on the user input.

?      Place the most selective search conditions at the beginning of the search condition. That way the database system may be able to complete the search before it has evaluated all the search conditions.

?      The search condition

columnx IN (1,13,24,...)

can be evaluated more efficiently by the SQL Optimizer than the semantically-identical search condition

columnx=1 OR columnx=13 OR columnx=24 OR ...

See also:

Analyzing Performance Bottlenecks

Monitoring

Troubleshooting

SAP Note 819641