topic Performance Tunning - VBAK and VBFA in Application Development and Automation Discussions

Performance Tunning - VBAK and VBFA

Former Member — Thu, 20 Jun 2013 12:06:16 GMT

Hi guys,

I have a doubt about performance tunning. In the company where I'm working, they had a problem with a RFC module. In that RFC, there was internal tables with header lines and first one select from VBAK, second, a select from VBFA for all entries in VBAK.

There was too many data because that select was about one specific date.

I debugged it a lot and I didn't find where was the slow point. I did trace and nothing show me exactly where was the problem...

In that RFC was a code like that:

LOOP AT LT_VBAK.

LOOP AT LT_VBFA

WHERE VBELN = LT_VBAK-VBELN

POSNV = LT_VBAK-POSNR

What I did to improve it:

1 - I did internal tables without header lines and declared work areas

2 - I remove FOR ALL ENTRIES and I put it in each select. Exemple:

LOOP AT LT_VBAK INTO SL_VBAK.

FREE LT_VBFA.

SELECT FIELD1 FIELD2...

FROM VBFA

INTO TABLE LT_VBFA

WHERE VBELN = SL_VBAK-VBELN

AND POSNV = SL_VBAK-POSNR

AND .......

LOOP AT LT_VBFA INTO SL_VBFA.

... " my logic

ENDLOOP.

Is is wrong? It improved a lot the performance, kind of... before, the RFC take 5 minutes to finish... Now, it takes 10 seconds.

One guy told me it is wrong because I did too many database access...

What do you think about it?

Thanks!

Re: Performance Tunning - VBAK and VBFA

FredericGirod — Thu, 20 Jun 2013 14:29:30 GMT

Hi Thiago

the rule is : less access to database, index access ...

for all entries make also several access to database

did you try to make an ST05 on your program and check the index used ? the full scan ? .. ?

(did you also update your stat on database)

regards

Fred

Re: Performance Tunning - VBAK and VBFA

ronaldo_aparecido — Thu, 20 Jun 2013 15:08:00 GMT

Hello

Really is wrong why are you making many accesses to the database

This is why you should not use select inside loop.

Select out of the 'loop' and use the 'read table' within the loop.

OBS:Use binary search in read.

SELECT FIELD1 FIELD2...

FROM VBFA

INTO TABLE LT_VBFA

for all entries in LT_VBAK

WHERE VBELN = LT_VBAK-VBELN

AND POSNV = LT_VBAK-POSNR

AND .......

sort LT_VBFA[] by VBELN ascending

POSNR ascending.

LOOP AT LT_VBAK INTO SL_VBAK.

read table LT_VBFA into ls_vbfa with key VBELN = Ls_VBAK-VBELN

POSNV = Ls_VBAK-POSNR

.............. binary search.

endloop.

Re: Performance Tunning - VBAK and VBFA

FredericGirod — Fri, 21 Jun 2013 04:16:18 GMT

Ronaldo,

make an SQL trace on you FOR ALL ENTRIES, and in the menu Summarize the entries. You will see the number of access of this statement.

second point, your

loop

read binary

it's good, but you cand do better

loop ...

read from index

if key VBAK > key VBFA

index = index + 1

if key VBAK = key VBFA

action

exit

if key VBAK < key VBFA

exit.

endif.

....

(I wrote really quickly before my breakfast )

Fred

Re: Performance Tunning - VBAK and VBFA

Former Member — Fri, 21 Jun 2013 06:33:30 GMT

Hi Thiago,

You should avoid writing the select within the loop....... So retrive the data and make use of parallel cursor istead of loop with in a loop.

Hope this helps you.

Re: Performance Tunning - VBAK and VBFA

Former Member — Fri, 21 Jun 2013 07:28:17 GMT

Hi,

Try with Parallel cursor method . so that performance will be increased better than writing LOOP AT WHERE ....

Ex : Using Parallel cursor

Both tables sorted by key K

I = 1.

LOOP AT ITAB1 INTO WA1.

LOOP AT ITAB2 INTO WA2 FROM I.

IF WA2-K <> WA1-K.

I = SY-TABIX.

EXIT.

ENDIF.

" ...

ENDLOOP.

Re: Performance Tunning - VBAK and VBFA

Former Member — Fri, 21 Jun 2013 07:39:31 GMT

Refer to this SAP OSS note 185530 - Performance Customer developments in SD. It may be helpful.

Rgds,

Re: Performance Tunning - VBAK and VBFA

Former Member — Fri, 21 Jun 2013 12:45:36 GMT

Hi guys

First, thank very much for all answers! Now after read I'm a bit ashamed because I didn't use Parallel Cursor to resolve it...

That RFC was big and a some guys made changes in that. It was something like 5.000 lines and when I trace it didn't find any slow point on ST05. When I debugged I found on slow part, and after many hours I finally found it:

LOOP AT t_vbap.

v_tabix_vbap = sy-tabix.

v_cont_rem = 0.

v_pend = 'X'.

LOOP AT t_vbfa_rem WHERE vbelv = t_vbap-vbeln

AND posnv = t_vbap-posnr

AND ( vbtyp_n = 'J' OR vbtyp_n = 'T').

IF t_vbap-shkzg <> 'X' AND t_vbfa_rem-vbtyp_n = 'J'.

v_cont_rem = v_cont_rem + 1.

ENDIF.

IF t_vbap-shkzg = 'X' AND t_vbfa_rem-vbtyp_n = 'H'.

v_cont_rem = v_cont_rem + 1.

ENDIF.

ENDLOOP.

v_qto_rem = 0.

v_vluni = 0.

IF v_cont_rem > 1.

LOOP AT t_vbfa_rem WHERE vbelv = t_vbap-vbeln

AND posnv = t_vbap-posnr.

IF t_vbap-shkzg <> 'X'.

CASE t_vbfa_rem-vbtyp_n.

WHEN 'J'.

ADD t_vbfa_rem-rfmng TO v_qto_rem.

WHEN 'N'.

ADD t_vbfa_rem-rfmng TO v_qto_rem.

WHEN 'M'.

SUBTRACT t_vbfa_rem-rfmng FROM v_qto_rem.

ENDCASE.

ELSE.

CASE t_vbfa_rem-vbtyp_n.

WHEN 'T'.

ADD t_vbfa_rem-rfmng TO v_qto_rem.

WHEN 'S'.

ADD t_vbfa_rem-rfmng TO v_qto_rem.

WHEN 'O'.

SUBTRACT t_vbfa_rem-rfmng FROM v_qto_rem.

ENDCASE.

ENDIF.

ENDLOOP.

IF v_qto_rem <> 0.

v_vluni = t_vbap-netwr / t_vbap-kwmeng.

t_pendente-vbeln = t_vbap-vbeln .

t_pendente-posnr = t_vbap-posnr .

t_pendente-kwmeng = v_qto_rem .

t_pendente-netwr = t_pendente-kwmeng * v_vluni.

t_pendente-kwmeng = v_qto_rem .

APPEND t_pendente.

CLEAR v_pend.

ENDIF.

v_cont_doc = 0.

v_par = 0.

LOOP AT t_vbfa_aux WHERE vbelv = t_vbap-vbeln

AND posnv = t_vbap-posnr.

v_cont_doc = v_cont_doc + 1.

ENDLOOP.

IF v_cont_doc > 0.

v_par = v_cont_doc MOD 2.

IF v_par NE 0.

CLEAR v_pend.

ENDIF.

IF v_cont_doc EQ v_cont_rem.

CLEAR v_pend.

ENDIF.

IF v_cont_rem EQ 1.

IF ( t_vbfa_rem-rfmng <> t_vbap-kwmeng ).

IF v_par NE 0.

IF t_vbap-kwmeng <> 0.

v_pend = 'X'.

ENDIF.

v_faturada = 0.

CLEAR t_vbfa_aux.

LOOP AT t_vbfa_aux WHERE vbelv = t_vbap-vbeln

AND posnv = t_vbap-posnr.

IF t_vbap-shkzg <> 'X'.

CASE t_vbfa_aux-vbtyp_n.

WHEN 'M'.

ADD t_vbfa_aux-rfmng TO v_faturada.

WHEN 'N'.

SUBTRACT t_vbfa_aux-rfmng FROM v_faturada.

ENDCASE.

ELSE.

CASE t_vbfa_aux-vbtyp_n.

WHEN 'O'.

ADD t_vbfa_aux-rfmng TO v_faturada.

WHEN 'S'.

SUBTRACT t_vbfa_aux-rfmng FROM v_faturada.

ENDCASE.

ENDIF.

ENDLOOP.

IF ( v_faturada <> t_vbap-kwmeng ).

v_pend = 'X'.

ELSE.

CLEAR: v_pend.

ENDIF.

IF ( v_par NE '0' AND v_cont_rem EQ 1 ) OR v_qto_rem EQ 0.

IF v_pend <> 'X'.

DELETE t_vbap INDEX v_tabix_vbap.

ENDIF.

ENDIF. " IF v_cont_doc > 0.

ENDLOOP. " LOOP AT t_vbap.

First I tought to use READ TABLE but I couldn't use it because I needed a LOOP (and that's the reason why I'm ashamed for didn't use parallel cursor).

Everyone is right in your answers but now I'm confused... Is it better I redo? I redo all 5.000 lines and it took me some days...

What problem can happen if I won't redo?

Thanks!

* I'll check this SAP note!

Re: Performance Tunning - VBAK and VBFA

ronaldo_aparecido — Fri, 21 Jun 2013 19:07:10 GMT

Fred its very cool you read thanks .

Re: Performance Tunning - VBAK and VBFA

ronaldo_aparecido — Fri, 21 Jun 2013 19:11:11 GMT

Shamili

Very cool !!!!!!!!!!!!!!!!!!!!!!!!!!!!!