Performance issue

[Former Member]

Hi all,

I am in need getting stock transfer eket-menge and stock transfer receipt quantity eket-wemng, based on material values, to get this i wrote a join statement which consists of ekko,ekpo and eket but it took long time to fetch values even in D server, i am not able to find any error with join statement which i wrote.

i appreciate if anybody give me tips, regarding the performance issue with that join statement and this is my join statement.

if not it_material[] initial.

select a_{matnr a}werks a_{lgort a}pstyp

b_{menge b}wemng b_{charg b}eindt

c_{ebeln c}RESWK c~BSART

into corresponding fields of table it_sto

from ekpo as a join eket as b

on a_{ebeln = b}ebeln

join ekko as c

on a_{ebeln = c}ebeln

for all entries in it_display

where a~matnr = it_display-matnr .

Thanks and regards,

babu

Message was edited by:

babu

[Former Member]

hi Babu,

What's the structure of your internal table it_sto? If it is same as what you wrote in your Select statement then you can write "into table" instead of "into corresponding fields of" .

Also, before using "FOR ALL ENTRIES", check if internal table is initial using

<b>If not it_display[] is initial.</b>

select a_{matnr a}werks a_{lgort a}pstyp

b_{menge b}wemng b_{charg b}eindt

c_{ebeln c}RESWK c~BSART

<b>into table it_sto</b>

from ekpo as a join eket as b

on a_{ebeln = b}ebeln

join ekko as c

on a_{ebeln = c}ebeln

for all entries in it_display

where a~matnr = it_display-matnr.

<b>endif.</b>

Hope this will help you.

Regards,

Vivek

[Former Member]

check if MATNR is an index on EKPO - if not you may need to add it or use extra selections so a valid index is used.

Also the join between EKPO and EKET - do these tables share more key fields? I thought both had EBELP (or POSNR?) and possibly further key fields in common, so this part of join should be:

from ekpo as a
join eket as b
on a~ebeln = b~ebeln
and a~ebelp = b~ebelp
and "(add any other fields)
join ekko as c ...

also, for readability, specifying "INNER JOIN" in the statements instead of just "JOIN" is a good idea.

Andrew

[Former Member]

Use of FOR ALL Entries

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Not Recommended

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

Recommended

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.