same select statement taking more time

[Former Member]

Hello all,

I have two select statements. only the name of table from where it is fetching records are different.

1) select belnr posnr etenr into corresponding fields of table it_cdtemp2

from j_3avasso for all entries in it_cdtemp1

where belnr = it_cdtemp1-vbeln and posnr = it_cdtemp1-posnr .

it_cdtemp1 has 100 entries and j_3avasso has 20000 entries

2) select belnr posnr etenr into corresponding fields of table it_cdtemp2

from j_3avap for all entries in it_cdtemp1

where belnr = it_cdtemp1-vbeln and posnr = it_cdtemp1-posnr .

it_cdtemp1 has 100 entries and j_3avasso has 2000 entries

statement 1 is executing less than a minute where as statement 2 is taking around 15 to 20 minutes

could anyone suggest why.. if so how to minimize run time

Regards

Bala

[Former Member]

1) Since you are using for all entries, check if the internal table is initial before the select query

2) Check if belnr and posnr which are in the where condition are primary keys fields of the table j_3avap. Try to include as many primary keys in the where condition

[Former Member]

Hi,

Try clear and refresh the table it_cdtemp2 before run the second Select or use APPENDING TABLE.

Hope this information is help to you.

Regards,

José

[Former Member]

dependencies:

- Number of records in for all entries table

- total number of records in databse from where you are fetching

- does the where clause contains key fields?

[Former Member]

Hi Bala,

I think members have suggested a lot of points which will solve your problem.

I would like to point out few things..

1.AVOID using into corresponding fields...instead make a structure with the required fields for both the selects.

2.ALWAYS check whether the table being used for 'FOR ALL ENTRIES' is not initial.

Hope it helps solve the pending issue.

Thanks.

[Former Member]

This message was moderated.

[Former Member]

Hi,

You can sort the internal table before using FOR ALL ENTRIES BY VBELN and POSNR.

This will save a lot of processing time.

You can also try combing both the selects as one join statement taking both the tables with for all entries addition.

Regards,

Subhashini

Edited by: Subhashini K on Oct 8, 2009 2:58 PM

[Former Member]

Its problem with index of second table and sorted ou after creating index for same