topic Re: Time execution using bypassing buffer in Application Development and Automation Discussions

Time execution using bypassing buffer

Former Member — Fri, 28 Oct 2011 10:38:23 GMT

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?

Re: Time execution using bypassing buffer

ThomasZloch — Fri, 28 Oct 2011 13:41:21 GMT

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

Re: Time execution using bypassing buffer

yuri_ziryukin — Mon, 31 Oct 2011 09:25:03 GMT

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).

Re: Time execution using bypassing buffer

Former Member — Mon, 31 Oct 2011 13:11:13 GMT

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

Re: Time execution using bypassing buffer

Former Member — Mon, 31 Oct 2011 13:46:01 GMT

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

Re: Time execution using bypassing buffer

yuri_ziryukin — Wed, 02 Nov 2011 08:48:10 GMT


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

Re: Time execution using bypassing buffer

Former Member — Wed, 02 Nov 2011 12:43:16 GMT

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.