performance issue of this code

[Former Member]

Hi,

Can any one tell me how to improve the performance of this code...?Or is there any way to alter select statements...?

Points guaranteed...

kaki

REPORT YMINVAGE no standard page heading

line-size 145

line-count 65

message-id Z1.

tables : mchb, "Batch Stocks

mara, "Material Master

mseg,

lqua. "Storage bin location

data: begin of t_mchb occurs 0,

matnr like mara-matnr,

werks like mchb-werks,

lgort like mchb-lgort,

ersda like mchb-ersda, " creation date

charg like mchb-charg, " Batch Number

clabs like mchb-clabs, " unrestricted quantity

cspem like mchb-cspem, " Blocked stock

prdha like mvke-prodh, " product hierarchy

wdatu like lqua-wdatu, " gr date

qty type i,

price(8) type p decimals 2,

amount(8) type p decimals 2,

end of t_mchb.

data: begin of t_qty occurs 0,

matnr like mchb-matnr,

werks like mchb-werks,

lgort like mchb-lgort,

wdatu like lqua-wdatu,

clabs like mchb-clabs,

cspem like mchb-cspem,

charg like mchb-charg,

price(8) type p decimals 2,

qty30 like mchb-clabs,

qty60 like mchb-clabs,

qty90 like mchb-clabs,

qtyful like mchb-clabs,

amt30 like mseg-dmbtr,

amt60 like mseg-dmbtr,

amt90 like mseg-dmbtr,

amtful like mseg-dmbtr,

end of t_qty.

data: begin of t_coll occurs 0,

matnr like mchb-matnr,

werks like mchb-werks,

lgort like mchb-lgort,

clabs like mchb-clabs,

cspem like mchb-cspem,

charg like mchb-charg,

amount(8) type p decimals 2,

date30 type d,

date60 type d,

date90 type d,

fdate type d,

end of t_coll.

data : zukurs type p decimals 3,

zffact like tcurr-ffact,

ztfact like tcurr-tfact.

selection-screen begin of block sel with frame title text-001.

select-options : s_matnr for mchb-matnr.

select-options : s_prdha for mara-prdha.

select-options : s_werks for mchb-werks.

select-options : s_lgort for mchb-lgort.

select-options : s_grdate for lqua-wdatu.

selection-screen end of block sel.

selection-screen begin of block sel1 with frame title text-002.

parameters: p_actstk radiobutton group g1 default 'X',

p_blkstk radiobutton group g1.

selection-screen end of block sel1.

start-of-selection.

perform get_data.

perform process_data.

perform display_data.

top-of-page.

perform set_page_header.

&----

---

*& Form process_data

&----

---

• text

----

---

form get_data.

if p_actstk = 'X'.

*for material,plant,location

select matnr werks lgort ersda charg clabs from mchb into

corresponding fields of table t_mchb

where matnr in s_matnr and

werks in s_werks and

lgort in s_lgort and

clabs > 0 and

ersda in s_grdate.

else.

select matnr werks lgort ersda charg cspem from mchb into

corresponding fields of table t_mchb

where matnr in s_matnr and

werks in s_werks and

lgort in s_lgort and

cspem > 0 and

ersda in s_grdate.

endif.

loop at t_mchb.

*for product hierarchy

select single * from mara where matnr = t_mchb-matnr and

prdha in s_prdha.

t_mchb-prdha = mara-prdha.

• for date of goods receipt

select single * from lqua where matnr = t_mchb-matnr and

charg = t_mchb-charg and

wdatu in s_grdate.

t_mchb-wdatu = lqua-wdatu.

• for unit price

select single * from mseg where matnr = t_mchb-matnr and

charg = t_mchb-charg and

werks = t_mchb-werks.

clear zukurs.

perform get_usd.

if zukurs <> 0.

t_mchb-price = ( mseg-dmbtr / mseg-menge ) / ( zukurs / 100 ).

else.

t_mchb-price = ( mseg-dmbtr / mseg-menge ) / '1.6'.

endif.

if sy-subrc = 0.

modify t_mchb.

clear t_mchb.

endif.

endloop.

if sy-subrc ne 0.

message i016.

leave list-processing.

endif.

loop at t_mchb.

t_coll-matnr = t_mchb-matnr.

t_coll-werks = t_mchb-werks.

t_coll-lgort = t_mchb-lgort.

append t_coll.

clear t_coll.

endloop.

sort t_coll.

delete adjacent duplicates from t_coll.

endform. "get_data

&----

---

*& Form process_data

&----

---

• text

----

---

form process_data.

data:age type d.

data: g_qty30 type i,

g_qty60 type i,

g_qty90 type i,

g_qtyful type i,

g_amt30 like t_qty-amt30,

g_amt60 like t_qty-amt30,

g_amt90 like t_qty-amt30,

g_amtful like t_qty-amt30.

loop at t_coll.

loop at t_mchb where matnr = t_coll-matnr

and werks = t_coll-werks

and lgort = t_coll-lgort.

age = t_mchb-wdatu.

if age lt 31.

if p_actstk = 'X'.

g_qty30 = g_qty30 + t_mchb-clabs.

else.

g_qty30 = g_qty30 + t_mchb-cspem.

endif.

elseif age gt 30 and age lt 61.

if p_actstk = 'X'.

g_qty60 = g_qty60 + t_mchb-clabs.

else.

g_qty60 = g_qty60 + t_mchb-cspem.

endif.

elseif age gt 60 and age lt 91.

if p_actstk = 'X'.

g_qty90 = g_qty90 + t_mchb-clabs.

else.

g_qty90 = g_qty90 + t_mchb-cspem.

endif.

else.

if p_actstk = 'X'.

g_qtyful = g_qtyful + t_mchb-clabs.

else.

g_qtyful = g_qtyful + t_mchb-cspem.

endif.

endif.

endloop.

• for amount calculation

• if t_mchb-price = '0.00'.

• t_mchb-price = '1'.

• endif.

g_amt30 = g_qty30 * t_mchb-price.

g_amt60 = g_qty60 * t_mchb-price.

g_amt90 = g_qty90 * t_mchb-price.

g_amtful = g_qtyful * t_mchb-price.

t_qty-amt30 = g_amt30.

t_qty-amt60 = g_amt60.

t_qty-amt90 = g_amt90.

t_qty-amtful = g_amtful.

t_qty-matnr = t_mchb-matnr.

t_qty-werks = t_mchb-werks.

t_qty-lgort = t_mchb-lgort.

t_qty-wdatu = t_mchb-wdatu.

t_qty-clabs = t_mchb-clabs.

t_qty-cspem = t_mchb-cspem.

t_qty-price = t_mchb-price.

*for quantities

t_qty-qty30 = g_qty30.

t_qty-qty60 = g_qty60.

t_qty-qty90 = g_qty90.

t_qty-qtyful = g_qtyful.

if g_qty30 ne 0 or g_qty60 ne 0

or g_qty90 ne 0 or g_qtyful ne 0.

append t_qty.

endif.

clear t_qty.

clear: g_qty30, g_qty60, g_qty90, g_qtyful,

g_amt30, g_amt60, g_amt90, g_amtful.

endloop.

endform. "process_data

&----

---

*& Form display_data

&----

---

• text

----

---

form display_data.

data: l_ind value '1'.

loop at t_qty.

IF l_ind = '1'.

l_ind = '0'.

FORMAT COLOR COL_TOTAL INTENSIFIED OFF .

ELSE.

l_ind = 1.

FORMAT COLOR COL_NORMAL.

ENDIF.

write:/1(24) t_qty-matnr color 2,

24(4) t_qty-werks,

32(4) t_qty-lgort.

WRITE:41(1) SY-VLINE NO-GAP.

WRITE:68(1) SY-VLINE NO-GAP.

WRITE:95(1) SY-VLINE NO-GAP.

WRITE:119(1) SY-VLINE NO-GAP.

• if age < 31.

write: 42(13) t_qty-qty30,

55(13) t_qty-amt30.

• elseif age < 61.

write: 69(11) t_qty-qty60,

84(11) t_qty-amt60.

• elseif age < 91.

write: 96(11) t_qty-qty90,

108(11) t_qty-amt90.

• else.

write: 120(11) t_qty-qtyful,

131(12) t_qty-amtful.

• endif.

endloop.

uline.

endform. "display_data

&----

---

*& Form GET_USD

&----

---

• text

----

---

form get_usd.

call function 'READ_EXCHANGE_RATE'

exporting

date = lqua-wdatu

foreign_currency = 'USD'

local_currency = 'SGD'

type_of_rate = 'M'

importing

exchange_rate = zukurs

foreign_factor = zffact

local_factor = ztfact

• valid_from_date =

exceptions

no_rate_found = 1

no_factors_found = 2

no_spread_found = 3

others = 4.

endform. " GET_USD

&----

---

*& Form set_page_header

&----

---

• text

----

---

FORM set_page_header.

• call function 'Z_REPORT_TITLE'

• EXPORTING

• line_size = sy-linsz

• sy_title = 'Inventory Aging Report'

• uline = 'X'

• first_page_only = ' '.

write:/1(145) space color 1.

write :1(20) 'Material' color col_heading,

23(9) 'Plant' color col_heading,

31(15) 'Str.loc' color col_heading,

51(18) '0 - 30 days' color col_heading,

76(18) ' 31 - 60 days ' color col_heading,

100(15) ' 61 - 90 days ' color col_heading,

125(14) ' > 91 days ' color col_heading.

write:/1(145) space color 1.

write:48(11) 'Qty' color col_heading,

58(10) 'Amnt(USD)' color col_heading.

write:73(10) 'Qty' color col_heading,

82(10) 'Amnt(USD)' color col_heading.

write:98(10) 'Qty' color col_heading,

108(10) 'Amnt(USD)' color col_heading.

write:122(10) 'Qty' color col_heading,

132(10) 'Amnt(USD)' color col_heading.

uline.

endform. "set_page_header

[Former Member]

U can avoid the selects with in the loop this will reduce ur performance.

select * from mara into t_mara for all entries in t_mchb where matnr = t_mchb-matnr and prdha in s_prdha.

Similarly u can change all the select statements and then u can read the itab inside the loop.

loop at t_mchb.
*for product hierarchy
<b>Read t_mara.........</b>
t_mchb-prdha = mara-prdha.


* for date of goods receipt
Read.....
t_mchb-wdatu = lqua-wdatu.

* for unit price
Read.....

This will improve ur performance better.

hope this helps.

[Former Member]

Kaki,

The Following Codes present in the program will cause severe performance degradation. So try to avoid it with the other possible logic.

Issue: 1:

-

---

<b>loop at t_mchb.</b>

*for product hierarchy

<b>select single * from mara where matnr = t_mchb-matnr and

prdha in s_prdha.</b>

t_mchb-prdha = mara-prdha.

• for date of goods receipt

<b>select single * from lqua where matnr = t_mchb-matnr and

charg = t_mchb-charg and

wdatu in s_grdate.</b>

t_mchb-wdatu = lqua-wdatu.

• for unit price

<b>select single * from mseg where matnr = t_mchb-matnr and

charg = t_mchb-charg and

werks = t_mchb-werks.

clear zukurs.</b>

perform get_usd.

.........

endloop.

<b>Remedy: 1:</b>

<b>Build Internal Tables and use it instead of Hitting the Database. </b>

So the Code would look like:

loop at t_mchb.

*for product hierarchy

<b>read table iMara with key</b>

matnr = t_mchb-matnr

prdha in s_prdha.

if sy-subrc eq 0.

t_mchb-prdha = mara-prdha.

endif.

• for date of goods receipt

<b>REad table iLqua with key</b>

matnr = t_mchb-matnr

charg = t_mchb-charg

wdatu in s_grdate.</b>

If sy-subrc eq 0.

t_mchb-wdatu = lqua-wdatu.

endif

• for unit price

<b>read table imseg with key</b>

matnr = t_mchb-matnr

charg = t_mchb-charg

werks = t_mchb-werks.

clear zukurs.

perform get_usd.

.........

endloop.

Thanks

Kam

[Former Member]

Hi Kaki,

Two immediate observations which would consume much time are, the

"into corresponding fields of" statement and select statements in loop statement.

If the order od the fields selected is the same as the order of the fields declared in the internal table, then you can avoid the into corresponding fields of statement.

To avoid the selects in the loops, you get the mara data into an internal table first and then use read table statement.

Regards,

Ravi

[jayanthi_jayaraman]

Hi,

I suggest you to change this part.

In this,you are using select single inside the loop at t_mchb.Instead of that you can modify those select statement using for all entries without loop.But this needs more internal tables.

If you want to use select inside loop,then change the

select single * from mara

as

select single prdha into t_mchb-prdha from mara...

And update other select single also accordingly.

Kindly reward points if it helps.

[Former Member]

Hi,

There are many performance related issues in this code.

1) Avoid using <b> into corresponding fields </b>in Select. This reduces most of time of execution.

2) Avoid using <b> Select inside loop </b>, instead select data outside loop , sort it and use<b> read</b> inside loop with binary search.

3) While using <b>sort</b>, avoid saying sort itab. instead specify some fields in which order u want to sort. <b> sort itab by field1 field2.</b>

4) while using delete adjacent duplicates also, mention the fields which it has to compare.

<b> Delete adjacent duplicates from itab comparing vbeln</b>.

Take care of all these points, then time of execution reduces a lot.

Regards,

Sailaja.

Dont forget to reward points if answer helps you.

[Former Member]

Hi,

You may perform a Runtime Analysis SE30 for some performance info on your system.

I think the biggest issues are:

1) Selecting from table LQUA with CHARG. LQUA does not have an index on CHARG field. Try to see if you can select differently using an available LQUA index, or you may have to create an LQUA with CHARG.

2) In the MSEG select, try to include LGORT also, because this gets closer to an availabe index on MSEG table.

3) Before the Loop at T_COLL, try to see if filling T_COLL first can be avoided. If not, use the parallel cursor method, like:

LOOP AT T_COLL.

LOOP AT T_MCHB FROM G_TABIX WHERE....

G_TABIX = SY-TABIX.

This should help you.

Bhanu