topic Re: same select statement taking more time in Application Development and Automation Discussions

same select statement taking more time

Former Member — Wed, 07 Oct 2009 16:42:55 GMT

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

Re: same select statement taking more time

Former Member — Wed, 07 Oct 2009 16:49:55 GMT

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

Re: same select statement taking more time

Former Member — Wed, 07 Oct 2009 19:10:54 GMT

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é

Re: same select statement taking more time

Former Member — Wed, 07 Oct 2009 19:25:33 GMT

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?

Re: same select statement taking more time

Former Member — Thu, 08 Oct 2009 09:12:56 GMT

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.

Re: same select statement taking more time

Former Member — Thu, 08 Oct 2009 09:23:37 GMT

This message was moderated.

Re: same select statement taking more time

Former Member — Thu, 08 Oct 2009 09:23:47 GMT

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

Re: same select statement taking more time

Former Member — Mon, 16 Nov 2009 10:41:02 GMT

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