topic Performence - SQL in a LOOP statement - Multiple hits to data base - Buffer in Application Development and Automation Discussions

Performence - SQL in a LOOP statement - Multiple hits to data base - Buffer

former_member202077 — Mon, 19 Jul 2010 15:24:58 GMT

Hi Experts,

I knew (by seeing the mentioned FM source code) that instead of hitting Data base with SQL every time, we can use standard SAP function module KNA1_SINGLE_READ (which gets the data from BUFFER) in perpsective of performence.

But, when I saw the KAN1 table's technical settings as BUFFERING NOT ALLOWED!!

1) Pls. let me know How that FM (KNA1_SINGLE_READ) makes sense to use in my report (where in there is SQL on KNA1 in a LOOP) with respect to performence? Whats the SAP idea?

Also, importantly I need to pull the data from VBFA table as multiple times in my report, some times in LOOP, so, I saw the below FMs

SD_VBFA_ARRAY_READ_VBELV

SD_VBFA_READ_WITH_VBELV

SD_VBFA_SELECT

SD_VBFA_SINGLE_READ

SD_VBFA_TRANSFER_TO_BUFFER

Same case with this VBFA table that, BUFFERING IS NOT ALLOWED!!

2) So, pls. let me know whether the above VBFA function modules also uses buffer (but, i did not see buffer stuff in the source code of FMs).

3) Can I go ahead and use these FMs in LOOP statements of my report by taking grant that, they are also using BUFFER - pormence perspective?

4) So, Does it mean that the BUFFER (Technical settings of a TABLE) is nothing to do this buffer (what SAP mentioned in source code of KNA1_SINGLE_READ)?

Thank you

Re: Performence - SQL in a LOOP statement - Multiple hits to data base - Buffer

Former Member — Mon, 19 Jul 2010 19:03:01 GMT

Look at FM KNA1_SINGLE_READ more closely. It isn't reading from the database buffer. It reads a record from KNA1 and saves it in an internal table. Then if you try to read the same record again, it reads it from the internal table instead.

Rob

Re: Performence - SQL in a LOOP statement - Multiple hits to data base - Buffer

former_member182114 — Mon, 19 Jul 2010 19:30:11 GMT

If your intention is to use the buffered access to read data to a report, maybe it's not the good approuch.

Use of buffer is desired when you run the same info many times, nof if you need it only once to fullfill the report and than waste buffer memory.

1) Pls. let me know How that FM (KNA1_SINGLE_READ) makes sense to use in my report (where in there is SQL on KNA1 in a LOOP) with respect to performence? Whats the SAP idea?

- use the SQL to fill an internal table (type sorted) outside the LOOP

- inside the loop you use READ TABLE

Also, importantly I need to pull the data from VBFA table as multiple times in my report, some times in LOOP, so, I saw the below FMs

- the same idea to fetch everything that you can outside the loop into internal tables

Hope this help you.

Regards, Fernando Da Ró

Re: Performence - SQL in a LOOP statement - Multiple hits to data base - Buffer

former_member202077 — Tue, 03 Aug 2010 02:51:58 GMT

As Rob said its not DB buffer.