topic Re: Performance Issue in Select Statement (For All Entries) in Application Development and Automation Discussions

Performance Issue in Select Statement (For All Entries)

Former Member — Wed, 04 Jun 2014 11:04:56 GMT

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,

Re: Performance Issue in Select Statement (For All Entries)

alok_patra — Wed, 04 Jun 2014 11:09:36 GMT

This message was moderated.

Re: Performance Issue in Select Statement (For All Entries)

former_member187748 — Wed, 04 Jun 2014 11:11:58 GMT

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.

Re: Performance Issue in Select Statement (For All Entries)

Former Member — Wed, 04 Jun 2014 11:16:40 GMT

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

Re: Performance Issue in Select Statement (For All Entries)

RaymondGiuseppi — Wed, 04 Jun 2014 11:17:20 GMT

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

Re: Performance Issue in Select Statement (For All Entries)

Former Member — Wed, 04 Jun 2014 11:25:20 GMT

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

Re: Performance Issue in Select Statement (For All Entries)

Former Member — Wed, 04 Jun 2014 12:04:00 GMT

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

Re: Performance Issue in Select Statement (For All Entries)

Juwin — Wed, 04 Jun 2014 14:58:11 GMT

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

Re: Performance Issue in Select Statement (For All Entries)

Former Member — Wed, 04 Jun 2014 15:34:02 GMT

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

Re: Performance Issue in Select Statement (For All Entries)

Former Member — Wed, 04 Jun 2014 16:45:18 GMT

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,

Re: Performance Issue in Select Statement (For All Entries)

Former Member — Wed, 04 Jun 2014 16:47:32 GMT

Hi,

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

Regards,

Re: Performance Issue in Select Statement (For All Entries)

Jelena_Perfiljeva — Wed, 04 Jun 2014 18:23:35 GMT

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

Re: Performance Issue in Select Statement (For All Entries)

former_member194739 — Wed, 04 Jun 2014 20:35:53 GMT

Dear Ankit,

Try through given blog.

Regards,

Abbas.