topic Re: Select Query Perfoemance in Application Development and Automation Discussions

Select Query Perfoemance

Former Member — Fri, 23 Apr 2010 17:16:01 GMT

Hi,

The below Query is taking 3 minutes to execute. Can anybody suggest me how best I can optimize it.

Even after creating secondary indexes, it takes that much time.

select * from /bic/azfiafo0300 into table int_trns1

for all entries in int_mu2

where comp_code = int_mu2-comp_code

and ( bus_area = int_mu2-bus_area

or /bic/zorg = int_mu2-/bic/zorg

or costcenter = int_mu2-costcenter )

and calday ge int_mu2-datefrom

and calday le int_mu2-dateto.

Thank you.

Re: Select Query Perfoemance

Former Member — Fri, 23 Apr 2010 17:30:37 GMT

Hi,

Please specify the required fields in select statement instead of select *.

It improves the performance.

Thanks.

Ramya.

Re: Select Query Perfoemance

Former Member — Fri, 23 Apr 2010 17:34:25 GMT

No, I will be needing all the fields of my table.

There were just 60k records in the table and it takes such a huge time to fetch.

Looking for a better approach.

Re: Select Query Perfoemance

Former Member — Fri, 23 Apr 2010 18:14:18 GMT

Try this.

WHERE fields in the order of the secondary index fields.
indicate the secondary index to use in the SELECT

Edited by: David Funez on Apr 23, 2010 12:15 PM

Re: Select Query Perfoemance

Former Member — Fri, 23 Apr 2010 18:18:10 GMT

Hi David,

We cannot replace OR with IN here.

OR is used with all different fields and not the values for the same field.

Re: Select Query Perfoemance

Former Member — Fri, 23 Apr 2010 20:13:26 GMT

and calday ge int_mu2-datefrom

and calday le int_mu2-dateto.

Try using:

and calday beetwen int_mu2-date and int_mu2-dateto

and calday IN r_dates

you can use a new table for the range of dates to improve the performance:

DATA : BEGIN OF r_dates OCCURS 0,

sign TYPE c LENGTH 1,

option TYPE c LENGTH 2,

low TYPE c LENGTH 18,

high TYPE c LENGTH 18,

END OF r_dates.

r_dates-low = int_mu2-date.

r_dates-high = int_mu2-dateto.

r_dates-sign = 'I'.

r_dates-option = 'BT'.

append r_dates.

Re: Select Query Perfoemance

Former Member — Mon, 26 Apr 2010 14:58:11 GMT

Hi,

Instead of using logical expressions in the where clause, use ranges.

Thanks,

Wenonah

Re: Select Query Perfoemance

Former Member — Mon, 26 Apr 2010 17:09:35 GMT

Hi Veera,

Try to prepare 3 internal tables to perform a clean understandeable instruction to database:

Similar too:

int_mu2_aux1[] = int_mu2.
int_mu2_aux2[] = int_mu2.
int_mu2_aux3[] = int_mu2.

* looking for bus_area
sort int_mu2_aux1 by comp_code bus_area datefrom dateto.
delete adjacent duplicates from int_mu2_aux1 comparing comp_code bus_area datefrom dateto.
if int_mu2_aux1[] is not initial.
select * from /bic/azfiafo0300 into table int_trns1
for all entries in int_mu2_aux1
where comp_code = int_mu2_aux1-comp_code
  and bus_area = int_mu2_aux1-bus_area
  and calday between int_mu2_aux1-datefrom int_mu2_aux1-dateto.
endif.

* looking for internal customer organization
sort int_mu2_aux2 by comp_code /bic/zorg datefrom dateto.
delete adjacent duplicates from int_mu2_aux2 comparing comp_code /bic/zorg datefrom dateto.
if int_mu2_aux2[] is not initial.
select * from /bic/azfiafo0300 into table int_trns1
for all entries in int_mu2_aux2
where comp_code = int_mu2_aux2-comp_code
  and /bic/zorg = int_mu2_aux2-/bic/zorg
  and calday between int_mu2_aux2-datefrom int_mu2_aux2-dateto.
endif.

* looking for cost center
sort int_mu2_aux3 by comp_code costcenter datefrom dateto.
delete adjacent duplicates from int_mu2_aux3 comparing comp_code costcenter datefrom dateto.
if int_mu2_aux3[] is not initial.
select * from /bic/azfiafo0300 into table int_trns1
for all entries in int_mu2_aux3
where comp_code = int_mu2_aux3-comp_code
  and costcenter = int_mu2_aux3-costcenter
  and calday between int_mu2_aux3-datefrom int_mu2_aux3-dateto.
endif.

FREE: int_mu2_aux1, int_mu2_aux2, int_mu2_aux3.

* removing duplicates
sort int_trns1.
delete adjacent duplicates from int_trns1 comparing all fields.

Viewing in that way you may notice that 3 indexes are required to a performatic execution, but you can analyse each one according to number of entries in each auxiliary internal table.

In other hand, since you have a not so huge (60k) table you can fetch from database all records w/o filtering org keys and do it on abap but it may be not a good option since you must recheck it with non performatic binary find due to datefrom/dateto range.

Anyway, it's important to do the coding acording to data you have on int_trns1.

Hope this help you.

Regards, Fernando Da Rós

Also, tell us about the keys you used on your secondary index.

Edited by: Fernando Ros on Apr 26, 2010 7:12 PM