topic Re: Peformance on select statement in Application Development and Automation Discussions

Peformance on select statement

Former Member — Fri, 02 Mar 2007 22:22:50 GMT

I have written a code as below. The select statement is using all the key fields. But the performance is very bad. Can anybody suggest best alternated to this code. Where performance will be good.

Thanks in advance for help.

Regards,

Chandra Kumar

loop at i_zrat_tmp1 into wa_zrat_tmp1.

clear: v_prev_year.

v_prev_year = wa_zrat_tmp1-startdate - 365 + 3.

SELECT startdate enddate

vkorg kunag kunwe oicontnr werks matnr oic_mot actvolume

FROM ZRATABILITY APPENDING table i_zrat_prev

FOR all entries in i_zrat_tmp

WHERE ( startdate <= v_prev_year

AND enddate >= v_prev_year )

AND vkorg EQ i_zrat_tmp-vkorg

AND kunag EQ i_zrat_tmp-kunag

AND kunwe EQ i_zrat_tmp-kunwe

AND oicontnr EQ i_zrat_tmp-oicontnr

AND werks EQ i_zrat_tmp-werks

AND matnr EQ i_zrat_tmp-matnr

AND oic_mot EQ i_zrat_tmp-oic_mot.

endloop.

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 22:27:51 GMT

Is this going to fetch only one record each time ?

Hmm...check if your fields you are selecting are in the same order of the database table, also the fields in the where clause. That improves some times.

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 22:34:04 GMT

I have already used for all entries. Other then all entries on a table.

I also have another table from which i need to calculate the date and use in the select statement.

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 22:38:23 GMT

Hello Chnadra,

Bettre chnage the code like.

Add a field in the itab i_zrat_tmp1 for V_PREV_year.


loop at i_zrat_tmp1 into wa_zrat_tmp1.
clear: v_prev_year.
i_zrat_tmp1-v_prev_year = wa_zrat_tmp1-startdate - 365 + 3.
modify i_zrat_tmp1.
endloop.


SELECT startdate enddate
vkorg kunag kunwe oicontnr werks matnr oic_mot actvolume
FROM ZRATABILITY APPENDING table i_zrat_prev
FOR all entries in i_zrat_tmp
WHERE ( startdate <= v_prev_year
AND enddate >= v_prev_year )
AND vkorg EQ i_zrat_tmp-vkorg
AND kunag EQ i_zrat_tmp-kunag
AND kunwe EQ i_zrat_tmp-kunwe
AND oicontnr EQ i_zrat_tmp-oicontnr
AND werks EQ i_zrat_tmp-werks
AND matnr EQ i_zrat_tmp-matnr
AND oic_mot EQ i_zrat_tmp-oic_mot.

Hope this will definetly improve the performance.

if useful reward

Vasanth

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 22:38:47 GMT

hey, i read it thouroughly and corrected my statement.

In the loop of i_z1 you are writing the selecting but you are not using any other fields except the date and you are doing the for all entries. Rather i suggest you take the date field also in the first internal table for which you are doing the for all entries. That way you can eliminate the loop.

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 22:40:07 GMT

hey vasantha,

chandra is looping at a different table calculating the date & doing for all entries of another table and using this date. so, what you say is not possible.

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 22:58:11 GMT

For each entry of table i_zrat_tmp it will fetch one record.

In the select statement the fields are in the same order in the database and i have used all the key fields.

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 23:06:30 GMT

Hello,

Make this change


loop at i_zrat_tmp1 into wa_zrat_tmp1.
clear: v_prev_year.
v_prev_year = wa_zrat_tmp1-startdate - 365 + 3.

SELECT startdate enddate
vkorg kunag kunwe oicontnr werks matnr oic_mot actvolume
FROM ZRATABILITY APPENDING table i_zrat_prev
*FOR all entries in i_zrat_tmp  " Remove for all entries.
WHERE ( startdate LE v_prev_year  " -----
AND enddate GE v_prev_year )      " -----  
AND vkorg EQ i_zrat_tmp-vkorg
AND kunag EQ i_zrat_tmp-kunag
AND kunwe EQ i_zrat_tmp-kunwe
AND oicontnr EQ i_zrat_tmp-oicontnr
AND werks EQ i_zrat_tmp-werks
AND matnr EQ i_zrat_tmp-matnr
AND oic_mot EQ i_zrat_tmp-oic_mot.

endloop. 
IF this is also not serving then remove appending and use SELECT --- ENDSELECT
Regards,
Vasanth

Message was edited by:

Vasanth M

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 23:17:44 GMT

The key performance issue here is a SELECT is defined in a LOOP. Try to put your query outside the loop based upon your logic.

Why cant you have this field 'v_prev_year' in the table 'i_zrat_tmp'?

Create one field and modify the field with 'v_prev_year = wa_zrat_tmp-startdate - 365 + 3.'

And then, you do for all entries on i_zrat_tmp.

Thanks,

Santosh

Re: Peformance on select statement

Former Member — Fri, 02 Mar 2007 23:27:45 GMT

try USING the following query.

IF i_zrat_tmp[] IS NOT INITIAL. (Always use this while using FOR ALL ENTRIES)

SELECT startdate enddate

vkorg kunag kunwe oicontnr werks matnr oic_mot actvolume

FROM ZRATABILITY APPENDING table i_zrat_prev

FOR all entries in i_zrat_tmp

WHERE vkorg EQ i_zrat_tmp-vkorg

AND kunag EQ i_zrat_tmp-kunag

AND kunwe EQ i_zrat_tmp-kunwe

AND oicontnr EQ i_zrat_tmp-oicontnr

AND werks EQ i_zrat_tmp-werks

AND matnr EQ i_zrat_tmp-matnr

AND oic_mot EQ i_zrat_tmp-oic_mot.

ENDIF.

loop at i_zrat_tmp1 into wa_zrat_tmp1.

clear: v_prev_year.

v_prev_year = wa_zrat_tmp1-startdate - 365 + 3.

loop at i_zrat_prev into wa_zrat_prev where startdate <= v_prev_year

AND enddate >= v_prev_year

wa_new_itab = wa_zrat_prev.

append wa_new_itab to i_new_itab.

clear wa_new_itab.

endloop.

hope this will help.

Please close the thread if Solved.

Thanks

Ashwani

Re: Peformance on select statement

Former Member — Sat, 03 Mar 2007 00:23:37 GMT

please CLOSE the thread if solved.

Thanks,

Ashwani

Re: Peformance on select statement

Former Member — Tue, 06 Mar 2007 20:07:47 GMT

The question is answered thanks for all of you. Who replied