Practical approach to buffer the records in SAP .

Our today’s Topic is Buffering.We are sure many of you know about buffering concept as it increases the performance and reduces the database load. But today we will see a practical approach how does it exactly works and increases the performance. 🙂 

So Let’s begin our journey to learn about How Exactly buffering work in SAP.

First Let’s understand what is buffering?

Table buffering is used to increase performance when reading data from database table. Data from a buffered table is read from a local buffer on the application server thus reducing database access.

Types of buffering:

1. Single record buffering
2. Generic buffering
3. Full buffering.

Now here comes the tricky and interesting Part. 🙂 

Let’s start with single record buffering.                                                        

Only single records are read from the database and loaded into the table buffer.(But once the records are loaded into buffer it will read from Buffer unless we clear the buffer)

STEP1: Go to T-code AL12 to see the buffering details.

STEP2: Go to menu Statistics->Buffers->single key buffer->All object.

Once you select all objects we will have all records of single buffering.

 

Let’s check the table CSKA (cost element table with a chart of Account). Entries Field is an important field here which indicate how many single records are buffered (stored from Database to application server).as of now we have 16 records which we have buffered.

STEP3: Let’s check the Technical settings of CSKA table.It has single buffering Check in radio button.

STEP4: Go to menu options Statistics->Single Key buffering->Keys

This will give you which are the records we have in an Application server or we have buffered.

STEP5: Enter your table name CSKA and click on Show buffer Key button.

Here we can see that we have the records which are buffered.

RECNO   – buffer counter

BUFFERED KEY – it is a combination of Primary key (which we have defined in T-code SE11 for CSKA table).

120 –client, 9000– Chart of Accounts, Cost Element-0001130000

 So, this makes our key 12090000001130000 🙂

Now as we have seen a total number of records we have buffered is 16.

If you check the entries of the table in T-code SE16N you will get our keys which make buffer key in buffering key .here all the records are buffered only once.

Let’s buffer the record 0001131060 which is not yet buffered.

STEP6: Create a program to check the performance and activate the SQL trace and Buffer Trace.

 

Now go to T-code ST05 to activate the Trace (follow the below steps).

Enter your table name CSKA and Hit enter. Trace is activated.

 

STEP7: Now execute our program. Let’s read the entries from buffer first.

Now it’s time to see the records are buffered or not.So deactivate the Trace again to see the record by clicking deactivate. Click on Display trace now and execute.

Note: In this case, we are trying to read a record which is not in the buffer but present in the DB. First, if the select query contains a where condition other than primary fields then buffering is not used irrespective of the select query.  

If the select query contains where condition which is exactly same as the primary key fields, then if buffering is allowed on this table with buffering switched on, then first it tries to read from the buffer, if not found then reads it from the DB, if Db read is successful then writes the content in the buffer and then read that from the buffer and passed to the program .

If u observe the lines in the trace, first tries to read from buffer, it fails then an open/fetch from Db, then a load into buffer and then read from buffer.

Now let’s execute the Program again by activating Buffer Trace using T-code ST05 (follow the same steps to trace the buffer as above).

This time it has taken the Access time only 9 microseconds and the Records are being read from Buffer.(so if records are not there it will first read from DB and if it is present in buffer it will read from buffer)

Now check the entries in T-code AL12 (Repeat the step 4 to 5 to see buffer entries)

Here we have the record which we  buffered through the program.

 

STEP8: it’s time to see the time execution of Direct DB access entries. 🙂

(Repeat the step 6 to 7).

1) Activate the trace.

2) Execute the program ZTEST_123

3) Deactivate the trace again.

Just look at the total micro sec  in first case 3,891 😦   and if you see the “Logical DB Connection Name ” it will be showing as R/3 i.e the Records are selected from the database.

See by your self the difference between the first approach and the approach suggest by us 🙂

A Huge amount of difference  🙂

Hope you have enjoyed learning the new approach for buffering the table in sap. We need your comment and suggestion.

Please share this post !!

Happy  Sharing and Happy learning !!