Time execution using bypassing buffer

[Former Member]

Hi!

I´m testing a performance of program. When i logon in system, I enter in SE30 and execute my program. In one select return to me 47.669.083 milisec. This select has BYPASSING BUFFER clause.

When i execute this program again in SE30, this return to me 5.452.061 milisec. in same select.

Why this happen? BYPASSING BUFFER doesn´t work?

[ThomasZloch]

This statement affects buffering in shared memory on the application server. There might be more levels of buffering/caching involved, e.g. on the database, that are not influenced by this statement.

It will take one of our real database buff(er)s in this forum to explain this in detail.

Thomas

[yuri_ziryukin]

As mentioned by Thomas, the BYPASSING BUFFER addition avoids the usage of the SAP application server buffer always reading from the database.

The database itself has its own buffer (data cache) which is used to operate on data blocks. During the processing of a SELECT statement all involved data blocks are read from disk and placed into the buffer if they are not already there. Other statements will displace blocks from cache following simplier or more complex LRU-algorythms. If you execute your statement for the second time with a small delay, there is a high chance that many blocks are still in DB cache and do not have to be read from disk. That's why your second execution is much faster.

But if you wait for minutes/hours before executing the statement again, the corresponding blocks will be displaced from the cache and the next execution will be long again (due to the disk accesses).

[Former Member]

Has any way to calculate time without any buffer? I try logoff and logon but not work, time was reduced.

[Former Member]

Execute transaction ST05 in parallel with both SELECTs and then compare the explains.

(Not entirely sure this will work, but it may be worth a try.)

On second thought, I doubt if this will help.

Rob

Edited by: Rob Burbank on Oct 31, 2011 9:55 AM

[yuri_ziryukin]

Has any way to calculate time without any buffer? I try logoff and logon but not work, time was reduced.

Hello Rodrigo,

yes, there is a way. Wait for the long time (ideally one day or so) and before executing the coding switch the SQL trace on. Then you will have "documented" time of the first execution.

Regards,

Yuri

[Former Member]

the time difference comes from the fact that the first SELECT rwad from the db discs and the second from the db cache.

Sooner or later the data will not be in the cache and you will need the longer time again.

Table buffer is a different topic, I advise you to consult the sap.help first and come then back with a more specific question.

There are reasons why BYPASSING BUFFER is needed. But sometimes it is a bug. If the data would come from the buffer the time could go down by a factor of 10 or more.