Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Query Optimisation

Former Member
0 Kudos

hello,

i have the following query which takes lot of time to fetch the data, which makes my Report very slow, can nyone help me to optimise this query if possible.

**************************************************

SELECT aarbpl aobjid bile01 bile02 bile03 bile04

bile05 bile06 bism01 bism02 bism03 bism04

bism05 bism06

INTO CORRESPONDING FIELDS OF TABLE i_act

FROM crhd AS a

INNER JOIN afru AS b ON

barbid = aobjid

WHERE a~arbpl IN s_arbpl AND

a~werks = p_werks AND

b~budat = p_budat AND

b~stokz <> 'X' AND

b~stzhl = '' .

my <i>s_arbpl contains fixed 12 values</i> only not more than that.

**************************************************

thanx

abhishek suppal

8 REPLIES 8

Former Member
0 Kudos

hi, first, don't use 'CORRESPONDING FIELDS', as a replace solution, define a internal table, which has the same sequence of fields as the select out fields from tables.

Then INTO TABLE XXX

And second, afru looks like a huge table, so select it in a join statement maybe not proper.

You can try to select out the associated data from crhd at first, then do select on afru with FOR ALL ENTRIES.

Through the select code turn to complex and redundant, maybe it can do good for the performance. You can try it.

At last, please pay attention to the INDEX of these two table, specially afru. If no appropriate INDEX can be used, you can create new INDEX for you select.(that need to coordinate with the BASIS, maybe)

Hope it will be helpful

former_member221770
Contributor
0 Kudos

Hi Abhishek,

Zhenglin is correct. Take a look at the Indexes via SE11 and try and use one of the standard one. If there isn't an appropriate index, then you may want to consider creating your own custom index. Also the INTO CORRESPONDING FIELDS puts alot of overhead on your query.

Anyway, would probably recommend splitting your query because CRHD can be quite bid and AFRU is definately VERY big.

try something along these lines....

data: begin of tbl_crhd occurs 0.

include structure crhd. "Note,only put your relevant fields here!

data: end of tbl_crhd.

data: begin of tbl_afru occurs 0.

include structure afru. "only relevant fields here too!

data: end of tbl_afru.

  • Use Index A in CRHD

select *

into table tbl_crhd

from crhd

where werks = p_werks and

arbpl in s_arbpl.

check sy-subrc eq 0.

  • This statement will force the DB Optimiser to use Index 3 for AFRU

select *

into table tbl_afru

from afru

for all entries in tbl_crhd

where arbid = tbl_crhd-objid.

check sy-subrc eq 0.

delete tbl_afru where budat ne p_budat.

delete...and so on...

Let us know how you go with this.

Also any luck with your other queries form your earlier post (MKPF/MSEG, AFRU/AFVC, etc)?

Cheers,

Pat.

Former Member
0 Kudos

hi pat,

thanx, i have tried this query but it takes lots of time as it reads whole the data from crhd table, so again slowing down my report,yes if get less number of records in crhd table then it works fine but i don't have any more selection criteria for crhd table,

regarding my previous queries i have solved that by making some mandatory selections and then fetching the data from mkpf and mseg tables. any ways thanx for rembering that query and showing such a concern.

abhishek suppal

Former Member
0 Kudos

As a split select solution ,the number of entries select from crhd will impact on the speed of select on afru.

If it is too many, the FOR ALL ENTRIES table will be large, and at last will be converted into many pieces of select statement by sap system, sent to the DB, so the performance will down.

I have an idea, don't restrict the select condition on afru too much, you can reduce some condition and select more entries from afru than you need.

Filter out the redundant entries in ABAP side, not in DB access.

If necessary, abandom the FOR ALL ENTRIES, try to use RANGE to replace the associated condition in FOR ALL ENTIRES.

Hope it will be helpful.

0 Kudos

i have tried this one also but it fetched huge data from afru after removing all restrictions from afru, i think i have to make index on arbid, werks and budat, then it can be optimised what u say.

abhishek suppal

0 Kudos

Abhishek,

Don't forget to reward points to the posts you found useful.

Cheers,

Pat.

Former Member
0 Kudos

Abhishek Suppal

The critical point to up your performance is too reduce the select statement send to the DB layer, if the FOR ALL ENTRIES table is too large, it will be explain to many pieces of select statement to DB, so it may not the effect we need.

Try to reduce the restriction on the select condition is in order to reduce the length of the select statement(not the code in ABAP, but the select statemtn explain by sap, you can see them in the ST05), hope you can get my thinking.

If that still unuseful, INDEX is the exclusive choose for you.

Thanks

Former Member
0 Kudos

Hi,

You can try using the standard index 'A' provided by SAP for table CHRD. the fields in index are WERKS, ARBPL.

So, try changing the order in where condition to -

WHERE a~werks = p_werks AND

a~arbpl IN s_arbpl

b~budat = p_budat AND

b~stokz <> 'X' AND

b~stzhl = '' .

Hope this helps.

With regards,

Sharath.