Performance Issue in Select Statement (For All Entries)

[Former Member]

Hello,

I have a report where i have two select statement

First Select Statement:

Select A B C P Q R

     from T1 into Table it_t1

          where ....

Internal Table it_t1 is populated with 359801 entries through this select statement.

Second Select Statement:

Select A B C X Y Z

     from T2 in it_t2 For All Entries in it_t1

          where A eq it_t1-A

             and B eq it_t1-B

             and C eq it_t1-C

Now Table T2 contains more than 10 lac records and at the end of select statement it_t2 is populated with 844003 but it takes a lot of time (15 -20 min) to execute second select statement.

Can this code be optimized?

Also i have created respective indexes on table T1 and T2 for the fields in Where Condition.

Regards,

[alok_patra]

This message was moderated.

[former_member187748]

Hi Ankit,

i don't know if you are taking data in your select statement from any cluster table or not (it seems), what you can do is firstly make more selection criteria to match in your first select query with respect to your screen -element, if you can make use of that table first from which you have more selection screen element can reduce the time at some extent.

Can you please tell us about the two table you are fetching your data.

[Former Member]

Hi,

My first table T1 is BKPF ( Standard SAP Table ) and table T2 is BSEG ( SAP Cluster Table )

Also i cannot query on BSEG table first.

Regards,

[Jelena_Perfiljeva]

Can't you use the index tables (like BSIS/BSAS) instead?

[Former Member]

Hi,

Before second select query try these followin methods,

Delete any duplicated entries in 1st internal table.

Use secondary index in table to supress performance issue.

You can use CALL FUNCTION "FMNAME" STARTING NEW TASK keyword to call second select query by  passing divided entries from first LT_1 table.

Regards,

Lingaraj

[Former Member]

Hi,

I have checked there are no duplicate records in internal table.

Regards,

[RaymondGiuseppi]

Yes to optimize the FOR ALL ENTRIES (if no JOIN/SUBQUERY solution available) which give mostly wrong performance issue with big internal table you can only manage indexes and technical parameters like blocking factor (48230).

You could also split the internal table and parallelize selection of data thru some asynchronous RFC call.

Regards,

Raymond

[Former Member]

Hi ankit,

Few things

1. Check it_t1 which is used for all entries has any duplicate records  - If more duplicates are there then sort and delete the duplicate records before second query.

2. Try with inner join - Sometime inner joins are faster

Thanks & Regards,

Arun

[Former Member]

Hi Ankit,

1.If is possible use key fileds as mandatory parameters or select-options in selection screen  and use in first select query.

2.Before second select query sort the 1st Internal table and delete adjacent duplicates and use some more conditions in second query .

Regards

Mahesh R

[Juwin]

If you have completed all the steps mentioned by others, in the above thread, and still you are facing issues then,.....

Use a Select within Select.

First Select Statement:

Select A B C P Q R package size 5000

     from T1 into Table it_t1

          where ....

Second Select Statement:

Select A B C X Y Z

     from T2 in it_t2 For All Entries in it_t1

          where A eq it_t1-A

             and B eq it_t1-B

             and C eq it_t1-C

do processing........

endselect

This way, while using for all entries on T2, your it_t1, will have limited number of entries and thus the 2nd select will be faster.

Thanks,

Juwin

[Former Member]

Hello Ankit,

Can you send execution plan? if you have already verified the what is the index(T2)? whether full scan/skip scan/unique scan.....

and what is the table statistics, storage index quality?

Regards,

Vadamalai A

[former_member194739]

Dear Ankit,

Try through given blog.

Regards,

Abbas.