Peformance on select statement

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

please CLOSE the thread if solved.

Thanks,

Ashwani

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

endloop.

hope this will help.

Please close the thread if Solved.

Thanks

Ashwani

[Former Member]

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