The performance of an ABAP program is largely determined by the efficiency of its database accesses. It is therefore worth analyzing your SQL statements closely. To help you to do this, you should use the Performance Trace and Runtime Analysis tools (Test menu in the ABAP Workbench). In particular, the SQL Trace in the Performance Trace shows you which parts of Open SQL statements are processed where, and how long they take.
To understand how SQL statements affect the runtime of ABAP programs, you need to understand the underlying system architecture. Thework processes of an application server are logged onto the database system (server) as users (clients) for as long as the R/3 System is running. The database management system (DBMS) forms the connection between users and data.
The general structure of a DBMS is as follows:
Within this architecture, there are four points that are important in respect of performance:
Reading and writing database files is the greatest bottleneck. The mark of a well-configured system is the speed of its I/O.
On a symmetrical multi-processor system, this is irrelevant.
Although unimportant for small data volumes, it becomes a bottleneck when large quantities of data are involved.
Each database system uses an optimizer whose task is to create the execution plan for SQL statements (for example, to determine whether to use an index or table scan). There are two kinds of optimizers:
Rule based optimizers analyze the structure of an SQL statement (mainly the SELECT and WHERE clauses without their values) and the table index or indexes. They then use an algorithm to work out which method to use to execute the statement.
Cost based optimizers use the above procedure, but also analyze some of the values in the WHERE clause and the table statistics. The statistics contain low and high values of the fields, or a histogram containing the distribution of data in the table. Since the cost based optimizer uses more information about the table, it usually leads to faster database access. Its disadvantage is that the statistics have to be periodically updated.
ORACLE databases up to and including release 7.1 use a rule-based optimizer. From Release 7.2 (R/3 Release 4.0A), they use a cost-based optimizer. All other database systems use a cost-based optimizer.
Rules for Efficient Open SQL Programming
Based on the above architecture, there are five rules that you should follow to make database accesses in ABAP programs more efficient. They apply in particular to the following database systems:
For the following database systems, they apply either in part or not at all:
The five rules are explained in the following sections:Keep the Result Set Small Minimize the Amount of Data Transferred Minimize the Number of Data Transfers Minimize the Search Overhead Reduce the Database Load