Performance Tunning - VBAK and VBFA

Former Member · ‎2013 Jun 20

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!

FredericGirod · ‎2013 Jun 20

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

ronaldo_aparecido · ‎2013 Jun 20

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.

FredericGirod · ‎2013 Jun 21

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

ronaldo_aparecido · ‎2013 Jun 21

Fred its very cool you read thanks .

Former Member · ‎2013 Jun 21

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.

Former Member · ‎2013 Jun 21

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.

ronaldo_aparecido · ‎2013 Jun 21

Shamili

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

Former Member · ‎2013 Jun 21

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

Rgds,

Former Member · ‎2013 Jun 21

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!

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Performance Tunning - VBAK and VBFA