Open SQL - SAP Buffering

When accessing a database table using Open SQL, SAP buffering is usually active if it is defined for the database table in question. The definition of a database table in ABAP Dictionary determines whether and how it is buffered. There are three buffering types:

• Single record buffering or partial buffering of individual records

• Generic Area buffering of specific areas

• Fully buffering of the whole table

With the exception of the statements listed below, read Open SQL statements access the buffer. Modifying Open SQL statements that make modifications using work areas access the buffer of the current application server directly and invalidate the entries affected in the buffer of the other application server. Modifications using UPDATE ... SET ... WHERE ... or DELETE ... WHERE ... invalidate the relevant entries in the buffers of all application servers. The invalidation of the entries on the other application servers does not happen directly and uses a buffer synchronization that is called periodically. The time interval can be set using the profile parameter rdisp/bufreftime and has a default value of two minutes. An access to an invalidated entry of a buffer will reload the entry from the database into buffer.

You can use the addition BYPASSING BUFFER of the SELECT statement to switch off access to the buffer explicitly. As well as specifying the BYPASSING BUFFER explicitly, SAP buffering is also avoided implicitly by some variants of Open SQL statements since the corresponding operations can only be carried out in the database and not in the SAP buffer. 

The following Open SQL statements avoid SAP buffering implicitly and access the database table directly:

• SELECT with the addition FOR UPDATE.

• SELECT with the addition DISTINCT,

• SELECT with aggregate expressions.

• Open SQL statement with the addition CLIENT SPECIFIED when the client ID is not specified in a WHERE condition.

• SELECT with JOIN expressions.

• Access to a table with individual record buffering without specifying (in the WHERE condition) all the equality conditions joined by AND for all the key fields of the primary key.

• Access to a generically buffered area without complete specification of equality conditions joined by AND in a WHERE condition.

• Open SQL statements with IS [NOT] NULL in the additions WHERE and HAVING.

• Use of a subquery in a WHERE condition.

• SELECT with FOR ALL ENTRIES in the following cases:

• Access to a table with individual record buffering if the internal table specified after FOR ALL ENTRIES contains more than one row and if a key field of the database table is compared with a component of the internal table. This case violates the condition for individual record buffering specified above, since the condition specified for FOR ALL ENTRIES is not passed to the database as AND; instead, it is passed as an OR condition.

• Access to a table with generic buffering if the requirement to specify the generic area exactly (as specified above) is violated. The condition after FOR ALL ENTRIES cannot produce an OR relationship between multiple generic areas.

• SELECT with the addition GROUP BY,

• SELECT with the addition ORDER BY, if individual columns are specified as the sort key, and these columns are not a left-aligned subset of the primary key in the correct order.

• After the invalidation of an entry in the buffer using a modifying statement, the next five read accesses that should have accessed the entry avoid the buffer of the current application server by default. The next read access to the changed entry reloads it into the buffer and removes the invalidation. The number of read accesses that avoid the buffer before the reload is specified in the profile parameter zcsa/sync_reload_c.

Note

The avoidance of the SAP buffer using the additions above should be noted for access to buffered data for performance reasons. To explicitly avoid the SAP buffer in theSELECT statement you should always use the BYPASSING BUFFER addition and not rely on the implicit behaviour of the additions above.