SAP Database table Buffering in SAP ABAP

SAP Database table Buffering in SAP ABAP

Table buffering is used to improve performance when fetching the data records of the table. The table buffers loaded locally on each application server in the system. The data of buffered tables can be retrieved directly from the buffer of the application server. This avoids the time-consuming process of accessing the database.

How to Create SAP Database Table

The buffering type defines which table records are loaded into the buffer of the application server when a table record is accessed. 

Buffering Types

Full buffering:

The system loads all the records of the table into the buffer when one record of the table is accessed.

Generic buffering:

When a record of the table is accessed, all the records having this record in the generic key fields are loaded into the buffer.

Single-record buffering:

Only the records of a table that are really accessed are loaded into the buffer.

What is the use of Database Table Buffering?

Buffering is particularly important in client/server environments, as it takes considerably longer to access a table with the network than it does to access a table that is buffered locally. 

How the Buffers Loaded with Program?

If a program accesses the data of a buffered table, the database interface determines whether this data is in the buffer of the application server. If this is the case, the data is read directly from the buffer. If the data is not in the buffer of the application server, the system reads it from the database and loads it into the buffer. The next access to this data can then use the buffer.

Synchronization of the Local Buffers

A synchronization mechanism runs at a fixed time interval, for example every 1-2 minutes. The system reads the log table and invalidates the buffer contents that were changed by other servers. In the next access, the system reads the data of invalidated tables directly from the database and updates it in the buffer.

Displacement

If more space is required in the buffer due to new data, the system displaces the data that has not been accessed for the longest time. 

How to reset the Table Buffers

We can reset the table buffers on the corresponding application servers by entering $TAB in the command field. The system invalidates all the data in the buffer.

Restrictions and Recommendations on Table Buffering

Only transparent tables and pooled tables can be buffered. Cluster tables cannot be buffered.

The data read by the application must always be up-to-date. Delays caused by the synchronization mechanism cannot be accepted .

Example:

The table containing currency exchange rates is updated only once a day, but it is read frequently. Buffering is recommended in this case.

Technical Implementation of Table Buffers

The table buffers lie in shared memory. There is a single-record table buffer TABLPand a generic/full table buffer TABL. The two table buffers differ primarily in how they manage free storage areas and in their displacement mechanisms.

Accesses Proceeding Directly to the Database

When your program accesses to buffered tables, you must know which accesses read from the buffer and which accesses go directly to the database.

SELECT... BYPASSING BUFFER

SELECT FOR UPDATE

SELECT with aggregate function, for example COUNT, MIN, MAX, SUM, AVG

SELECT DISTINCT

SELECT... WHERE... IS NULL

SELECT with GROUP BY or HAVING clause

SELECT with sub queries or joins

ORDER BY (with the exception of PRIMARY KEY)

With generic buffering, all SELECT statements that do not fully specify the generic key also proceed directly to the database. With generic buffering, the buffer can only be used for accesses that specify the complete generic key.

With single-record buffering, each SELECT statement that does not specify the complete key proceeds directly to the database.

Analyzing Buffer Quality

In the SAP Menu, choose Administration → System Administration → Monitor → Performance → Setup/Buffers → Buffers.

An overview of all the buffers and database calls appears.

SAP Architecture
SAP Table Technical Settings