Reduce the Database Load

Unlike application servers and presentation servers, there is only one database server in your system. You should therefore aim to reduce the database load as much as possible. You can use the following methods:

Buffer Tables on the Application Server

You can considerably reduce the time required to access data by buffering it in the application server table buffer. Reading a single entry from table T001 can take between 8 and 600 milliseconds, while reading it from the table buffer takes 0.2 - 1 milliseconds.

Whether a table can be buffered or not depends its technical attributes in the ABAP Dictionary. There are three buffering types:

• Resident buffering (100%) The first time the table is accessed, its entire contents are loaded in the table buffer.
• Generic buffering In this case, you need to specify a generic key (some of the key fields) in the technical settings of the table in the ABAP Dictionary. The table contents are then divided into generic areas. When you access data with one of the generic keys, the whole generic area is loaded into the table buffer. Client-specific tables are often buffered generically by client.
• Partial buffering (single entry) Only single entries are read from the database and stored in the table buffer.

When you read from buffered tables, the following happens:

1. An ABAP program requests data from a buffered table.
2. The ABAP processor interprets the Open SQL statement. If the table is defined as a buffered table in the ABAP Dictionary, the ABAP processor checks in the local buffer on the application server to see if the table (or part of it) has already been buffered.
3. If the table has not yet been buffered, the request is passed on to the database. If the data exists in the buffer, it is sent to the program.
4. The database server passes the data to the application server, which places it in the table buffer.
5. The data is passed to the program.

When you change a buffered table, the following happens:

1. The database table is changed and the buffer on the application server is updated. The database interface logs the update statement in the table DDLOG. If the system has more than one application server, the buffer on the other servers is not updated at once.
2. All application servers periodically read the contents of table DDLOG, and delete the corresponding contents from their buffers where necessary. The granularity depends on the buffering type. The table buffers in a distributed system are generally synchronized every 60 seconds (parameter: rsdisp/bufreftime).
3. Within this period, users on non-synchronized application servers will read old data. The data is not recognized as obsolete until the next buffer synchronization. The next time it is accessed, it is re-read from the database.

You should buffer the following types of tables:

• Tables that are read very frequently
• Tables that are changed very infrequently
• Relatively small tables (few lines, few columns, or short columns)
• Tables where delayed update is acceptable.

Once you have buffered a table, take care not to use any Open SQL statements that bypass the buffer.

The SELECT statement bypasses the buffer when you use any of the following:

• The BYPASSING BUFFER addition in the FROM clause
• The DISTINCT addition in the SELECT clause
• Aggregate expressions in the SELECT clause
• Joins in the FROM clause
• The IS NULL condition in the WHERE clause
• Subqueries in the WHERE clause
• The ORDER BY clause
• The GROUP BY clause
• The FOR UPDATE addition

Furthermore, all Native SQL statements bypass the buffer.

Avoid Reading Data Repeatedly

If you avoid reading the same data repeatedly, you both reduce the number of database accesses and reduce the load on the database. Furthermore, a "dirty read" may occur with database tables other than Oracle. This means that the second time you read data from a database table, it may be different from the data read the first time. To ensure that the data in your program is consistent, you should read it once only and then store it in an internal table.

Sort Data in Your ABAP Programs

The ORDER BY clause in the SELECT statement is not necessarily optimized by the database system or executed with the correct index. This can result in increased runtime costs. You should only use ORDER BY if the database sort uses the same index with which the table is read. To find out which index the system uses, use SQL Trace in the ABAP Workbench Performance Trace. If the indexes are not the same, it is more efficient to read the data into an internal table or extract and sort it in the ABAP program using the SORT statement.

Use Logical Databases

SAP supplies logical databases for all applications. A logical database is an ABAP program that decouples Open SQL statements from application programs. They are optimized for the best possible database performance. However, it is important that you use the right logical database. The hierarchy of the data you want to read must reflect the structure of the logical database, otherwise, they can have a negative effect on performance. For example, if you want to read data from a table right at the bottom of the hierarchy of the logical database, it has to read at least the key fields of all tables above it in the hierarchy. In this case, it is more efficient to use a SELECT statement.