Single Buffering Open SQL


 

Step0. Go to tcode- AL12 to see SAP Buffering details.

Step1. This time lets see how the single buffering works on a table and improve the read performance  in terms of time. Navigate along the highlighted path.

Step2. So here we have list of table. Just for the use case let’s take the table NACH. Currently four records are buffered.

Step3. Navigate along the path.

Step4. Provide the table name and display.

Step5. So a total 4 records of the table are in the buffered area. Also the read counter. up to now we have 4 read counter on the table.

Step6. Form the menu path navigate along the path to see what all records of the table NACH are buffered.

Step7. Provide the table name and click on display.

Step8. Here we have four records are in the buffer and a total of 18 records are present in the table. Buffered key is a combination of all primary fields. so here 001- is the mandt i.e client.

Step9. Technical details of NACH table in SE11. This is a single record buffering with switched on.

Step10.  Lets have the program to read a single record which is present in buffer.

Step11. To trace whether the program reads the record from the buffer or from the DB, we have to trace it in Tcode- ST05.  Select SQL trace and Buffer trace. Click on the button Activate trace with filter. From the pop up screen provide the user name, program name and the table name & continue.

Step12. Trace is activated.

Step13. Run the above report and select the first option to read it form the buffer.

Step14. Os here we have the O/P.

Step15. Go to ST05 and deactivate the trace and display trace.

Step16. Execute it to display.

Step17. So here the Conn Name is blank means the record is read from the buffer not from the DB.

It takes around 38 micro seconds to read a single record from the buffer.

Step18. Navigate along the menu path to see the consolidated details of the trace.

Step19. So here is the trace details when the record is read from the buffer not from the db.

Step20. previously the read counter was 4 and now after one read from the buffer the read counter has increased to 5.

Step21.  Activate the trace in ST05.

Run the program and this time trying to read the same record form the db not from buffer.

Step22. So here is the output.

Step23. Display the trace. So here the CONN column carries value R/3 means the record is selected from the DB not from buffer. It takes a time of 4201 micro second to select it.

Step24. Navigate along the menu path.

Step25. So here the details.

Step26. Comparison for the same record selection one from buffer and later form DB. So reading from the DB is time expensive.

Step27.  This time we will try to read a record which is not yet in the  buffer  but exists in the DB. Select the high lighted record for the use case.

Step28. Make the where condition appropriate. Activate the trace with filter in ST05 tcode.

Step29. Execute it.

Step30. So here the O/P.

Step31. Display the trace. 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 form 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 then buffer and passed to the program . if u observe the lines in the trace, first tries to read from buffer, it fails then a open/fetch from Db, then a load into buffer and then read form buffer.

Step32. Navigate the menu option.

Step33. Here is the trace consolidated list.

Step34. From AL12 tcode home screen, select the menu path to see if the record is updated in the buffer.

Povide the table name.

So here the record updated in the buffer area.

Step35.  Navigate along the menu highlighted path.   

                                                                                


Provide the table name and display.

So previously we had 4 entries, now updated with one more. now 5 records are there in the buffer.

Also the read counter increased form 5 to 7. This time first a read triggered in the buffer but failed. Then record is taken from the DB and loaded into buffered & read again from the buffer making teh read counter as 7.

Step36. If you see here the entries is 5 and max entries is 5. What will happen if we try to read one more record form the DB which is not there in the buffer yet. Will again after reading from the DB a new entry will be added to the buffer or an existing entry will be replaced by the current one as entry and ma entry both are same.

Step37. Activate the trace in ST05. run the program with a different value in where condition.

Step38. Select the first option. Execute.

Step39. Here is the O/P.

Step40. So here is the trace result details.

Step41. So then a new entry is added in the buffer and entries and max entries are updated with read counter filed.


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s