SAP R/3 форум ABAP консультантов

[admin]

SELECT statements that bypass the table buffer

Data that is frequently accessed and rarely modified should be read from a table buffered on the application server, and not from the database. It is about 10-30 times faster to read one entry from a buffered table than to read it from the database cache – say it takes only 20µs instead of 400µs.
Again, things can go wrong when buffered tables are accessed inadequately; that is, the buffer may be bypassed and the performance advantage lost. But do not worry – there is another Code Inspector check at hand to help you. This article explains when it is best to buffer tables. It also describes the appropriate Code Inspector check to detect statements that bypass the buffer and how to correct the underlying problems.

When and how to buffer tables

Small or medium sized database table with data that is frequently read, but only rarely modified, should be buffered within SAP’s table buffer. In fact, there are two types of table buffer: one for single record buffered tables, and one for generically and fully buffered tables, but for simplicity let's refer to 'the (SAP) table buffer'. The best candidates for buffering are tables containing business configuration data; for example, a table containing the address data of a company's subsidaries. Since the address of a subsidary will not change frequently, and since there will be at maximum some hundred to thousand data sets, the table should be buffered.
Whether or not a table is buffered is defined in its technical settings (transaction SE11). You can choose between the following buffering types:
single record buffering to buffer single records, identified by the full primary key

generic buffering to buffer generic key areas with 1, 2, ..., n key fields, where n is shorter than the primary key length

full buffering to buffer the whole table; for client dependend tables, full buffering corresponds to generic buffering with respect to the client field

If data is modified on application server A it can take up to two minutes for the buffer entries to be invalidated on another application server B of the same system. During this interval, a user on application server B may read outdated information. For some critical applications this behavior can lead to data inconsistencies. Therefore, for these applications buffering must be avoided.

When a buffer range is invalidated by a modification on server A, the data is not reloaded from the database into the buffer of server B with the first read access. Instead, it takes up to ten read accesses until the database interface is 'confident' enough to re-fill the buffer with the data from the database. This 'pending' behavior of the buffer, plus the communication overhead created by the invalidation mechanism, is the reason why frequently modified tables should not be buffered.

SELECT statements that bypass the table buffer

For the table buffer to be quick in responding to a request, it works in a simple and straightforward fashion. The SAP table buffer is not aware of sophisticated things such as secondary indexes (it only knows the primary one), SQL Joins, aggregate functions, or complicated selection ranges. This is why there is a list of OpenSQL options that lead to an implicit bypassing of the buffer, because the buffer cannot handle them. Therefore, avoid the following options if you want to access data from buffered tables:

List of statements that bypass the SAP table buffer (as of July 2007):

Implicit bypassing caused by an option of the SELECT statement

SELECT from a single record buffered table without explicit use of ‘SELECT SINGLE’ (starting with SAP NetWeaver 7.10, the key word 'SINGLE' is no longer required to make use of the single record buffer)

SELECT from a buffered table in an Open SQL Join or use of a buffered table as a joined table in a database view defined in the data dictionary

SELECT with a subquery

SELECT with an aggregate function: COUNT(), MIN(), MAX(), SUM(), AVG()

SELECT DISTINCT ...

SELECT ... GROUP BY ... [HAVING cond ...]

SELECT ... ORDER BY ... where the sort order differs from the primary key

SELECT with option ‘CLIENT SPECIFIED‘, but no client field in the WHERE clause

SELECT ... WHERE a IS [NOT] NULL

Implicit bypassing caused by an incomplete WHERE clause or inappropriate buffer setting

Generic key not fully specified in the WHERE clause of a SELECT from a generically buffered table

Primary key not fully specified in the WHERE clause of a SELECT from a single record buffered table

Intended Bypassing

Use the option ‘BYPASSING BUFFER’ to avoid accessing outdated table buffer information, and to read data from the database instead. This will only be relevant for some special applications.

The statement ‘SELECT FOR UPDATE’ sets a database lock and therefore always has to bypass the table buffer.

Native SQL statements also bypass the SAP table buffer. Moreover, they do not trigger the buffer invalidation mechanism after a database change, and therefore should not be used in application programming.
Using any statement of the list above with a buffered table causes the SAP table buffer to be bypassed, thereby forgoing the performance advantage of the buffer access over a database access.