performance

[Former Member]

Hi friends,

I have given a program to reduce its perfromance, then i run that report in se30

it is showing the db hit at around 97%, then for this i have taken tables statement

and used types to declare internal tables, it was having lot of inner joins i have

removed that and used for all entries, now i run the tranction se30 to this modified report but also it is showing the db hit is around 93 %.

Still in this report he is using lot of loops, inside loops he is using lot of select

queries, for this i used read insted of select inside loops but in runtime analysis

the db hit is incresed ie 98%.

Can any one help me i am sending piece of loop pls suggest how to reduce

select inside loop.

LOOP AT i_vbep.

CLEAR ig_tmatnr.

• Here Ig_tmatnr has all the materail with Materail type ne 'VERP'.

read table ig_tmatnr with key matnr = i_vbep-matnr.

if sy-subrc = 0.

refresh it_vbfa.

clear it_vbfa.

select vbelv

posnv

vbeln

rfmng

from vbfa

into table it_vbfa

where vbfa~vbelv = i_vbep-vbeln and

vbfa~posnv = i_vbep-posnr and

vbfa~vbtyp_n = 'J'.

if sy-subrc = 0.

refresh i_vbuk. clear i_vbuk.

select vbeln

wbstk

into table i_vbuk from vbuk for all entries in it_vbfa

where vbeln = it_vbfa-vbeln.

sort i_vbuk by vbeln.

loop at it_vbfa.

clear : l_delete_rec.

read table i_vbuk with key vbeln = it_vbfa-vbeln.

• binary search.

if sy-subrc = 0.

if i_vbuk-wbstk = 'C'.

• Must accumulate the Delivered QTYs - Use this Summed amount

• and deduct from initial Order Item QTY. This results in the

• actual OPEN ITEM QTY on the report; basically whats OPEN to be

• Delivered

w_deliv_cmp_rfmng = w_deliv_cmp_rfmng + it_vbfa-rfmng.

• Set the Delete flag because you don't need this delivery to show

• on the report as its already been goods issued! But you still want to

• Calculate its Delivered QTY for Outstanding QTYs

l_delete_rec = 'X'.

endif.

w_deliv_rfmng = w_deliv_rfmng + it_vbfa-rfmng.

if l_delete_rec is initial .

move-corresponding it_vbfa to i_vbfa.

append i_vbfa.

clear i_vbfa.

endif.

endif.

endloop.

ENDIF.

MOVE-CORRESPONDING i_vbep TO i_sales.

IF w_deliv_cmp_rfmng = 0.

i_sales-outstanding_qty = i_sales-kwmeng - w_deliv_rfmng.

ELSE.

• The 'new' Open Order Qty is the Initial Order QTY less the

• Delivered QTYS.

i_sales-kwmeng = i_sales-kwmeng - w_deliv_cmp_rfmng.

i_sales-outstanding_qty = i_sales-kwmeng - w_deliv_rfmng.

ENDIF.

READ TABLE ig_tmatnr WITH KEY matnr = i_vbep-matnr

werks = i_vbep-werks.

CHECK sy-subrc = 0.

MOVE ig_tmatnr-dispo TO i_sales-dispo.

CHECK i_vbep-kunnr IN s_kunnr.

READ TABLE i_vbpa WITH KEY vbeln = i_vbep-vbeln BINARY SEARCH.

CHECK sy-subrc = 0.

READ TABLE i_kunnr WITH KEY kunnr = i_vbpa-kunnr BINARY SEARCH.

MOVE i_vbpa-kunnr TO i_sales-kunnr.

MOVE i_kunnr-name1 TO i_sales-name1.

MOVE i_kunnr-land1 TO i_sales-land1.

READ TABLE i_mtxt1 WITH KEY matnr = i_vbep-matnr BINARY SEARCH.

MOVE i_mtxt1-maktx TO i_sales-maktx.

APPEND i_sales.

CLEAR i_sales.

ELSE.

DELETE i_vbep.

ENDIF.

CLEAR w_deliv_rfmng.

CLEAR w_deliv_cmp_rfmng.

ENDLOOP. " loop end for i_vbep

regards,

desha

[Former Member]

HI,

Please remove all the select statements from the loop.

and then u can use read statements instead of select statements in loop.

let me know if u have any doubts.

Regards

Suresh.D

[Former Member]

Hi,

Donot use loops at all which is having select statement inside!!!!

Instead select data into itabs with select statemetn and using joins (inner/outer)

To join different tables, it is easy for that refer following link which is having all details of link between tables.

http://www.sap-img.com/sap-download/sap-tables.zip

Hope this will help u, if u want more info revert back.

Jogdand M B

[sharadendu_agrawal]

Please avoid using the select queries inside the loop. And instead use the read statements taking the help of the work area. For selecting all the required try using for all entries in the select query.

Reward if useful

Cheers,

Sharadendu

[Former Member]

Hi,

Try changing your code this way...

refresh it_vbfa.

clear it_vbfa..

if not i_vbep[] is initial.

select vbelv

posnv

vbeln

rfmng

from vbfa

into table it_vbfa

for all entries in i_vbep

where vbelv = i_vbep-vbeln and

posnv = i_vbep-posnr and

vbtyp_n = 'J'.

endif.

refresh i_vbuk. clear i_vbuk.

If not it_vbfa[] is initial.

select vbeln

wbstk

into table i_vbuk from vbuk for all entries in it_vbfa

where vbeln = it_vbfa-vbeln.

endif.

sort i_vbuk by vbeln.

LOOP AT i_vbep.

CLEAR ig_tmatnr.

• Here Ig_tmatnr has all the materail with Materail type ne 'VERP'.

read table ig_tmatnr with key matnr = i_vbep-matnr.

if sy-subrc = 0.

loop at it_vbfa where vbelv = i_vbep-vbeln

And posnr = i_vbep-posnr.

clear : l_delete_rec.

read table i_vbuk with key vbeln = it_vbfa-vbeln.

• binary search.

if sy-subrc = 0.

if i_vbuk-wbstk = 'C'.

• Must accumulate the Delivered QTYs - Use this Summed amount

• and deduct from initial Order Item QTY. This results in the

• actual OPEN ITEM QTY on the report; basically whats OPEN to be

• Delivered

w_deliv_cmp_rfmng = w_deliv_cmp_rfmng + it_vbfa-rfmng.

• Set the Delete flag because you don't need this delivery to show

• on the report as its already been goods issued! But you still want to

• Calculate its Delivered QTY for Outstanding QTYs

l_delete_rec = 'X'.

endif.

w_deliv_rfmng = w_deliv_rfmng + it_vbfa-rfmng.

if l_delete_rec is initial .

move-corresponding it_vbfa to i_vbfa.

append i_vbfa.

clear i_vbfa.

endif.

endloop.

MOVE-CORRESPONDING i_vbep TO i_sales.

IF w_deliv_cmp_rfmng = 0.

i_sales-outstanding_qty = i_sales-kwmeng - w_deliv_rfmng.

ELSE.

• The 'new' Open Order Qty is the Initial Order QTY less the

• Delivered QTYS.

i_sales-kwmeng = i_sales-kwmeng - w_deliv_cmp_rfmng.

i_sales-outstanding_qty = i_sales-kwmeng - w_deliv_rfmng.

ENDIF.

READ TABLE ig_tmatnr WITH KEY matnr = i_vbep-matnr

werks = i_vbep-werks.

CHECK sy-subrc = 0.

MOVE ig_tmatnr-dispo TO i_sales-dispo.

CHECK i_vbep-kunnr IN s_kunnr.

READ TABLE i_vbpa WITH KEY vbeln = i_vbep-vbeln BINARY SEARCH.

CHECK sy-subrc = 0.

READ TABLE i_kunnr WITH KEY kunnr = i_vbpa-kunnr BINARY SEARCH.

MOVE i_vbpa-kunnr TO i_sales-kunnr.

MOVE i_kunnr-name1 TO i_sales-name1.

MOVE i_kunnr-land1 TO i_sales-land1.

READ TABLE i_mtxt1 WITH KEY matnr = i_vbep-matnr BINARY SEARCH.

MOVE i_mtxt1-maktx TO i_sales-maktx.

APPEND i_sales.

CLEAR i_sales.

ELSE.

DELETE i_vbep.

ENDIF.

CLEAR w_deliv_rfmng.

CLEAR w_deliv_cmp_rfmng.

ENDLOOP. " loop end for i_vbep

Regards,

Vidya.

[Former Member]

HI,

Try to remove SELECT queries with in the LOOP.

Kishi.