topic Re: Performance increase in Application Development and Automation Discussions

Performance increase

Former Member — Thu, 24 May 2012 03:56:44 GMT

Hi,

I have one condition that shouldbe satisfied for Fetching entries from a Z table which is as follows.

Dateto field can be between first and last day of consumption month or it can be greater or equal to last day.

Datefrom field can be between first and last day of consumption month or it can be less than or equal tofirst day .

i have written the following code for that but i have to reduce the number of condition for performance issue pleas suggest me if there is any better method for this.

SELECT logikzw profrole profile
    FROM eprofass
    INTO TABLE git_eprofass
    WHERE profrole EQ 'ESOG'   AND
         ( ( dateto BETWEEN p_x_setlmnth_firstday AND p_x_setlmnth_lastday ) OR ( dateto GE p_x_setlmnth_lastday ) )   AND " GE p_x_setlmnth_lastday              AND
         ( ( datefrom BETWEEN p_x_setlmnth_firstday AND p_x_setlmnth_lastday ) OR ( datefrom LE p_x_setlmnth_firstday ) ).   " LE p_x_setlmnth_firstday.

Thanks in advance,

Chetan

Thanks T

Re: Performance increase

former_member282968 — Thu, 24 May 2012 05:27:11 GMT

This message was moderated.

Re: Performance increase

yuri_ziryukin — Thu, 24 May 2012 06:52:57 GMT

Hello Chetan,

your conditions look unnecessary complicated.

Look at the first part:

( ( dateto BETWEEN p_x_setlmnth_firstday AND p_x_setlmnth_lastday ) OR ( dateto GE p_x_setlmnth_lastday ) )

You can easily reduce it to

dateto GE p_x_setlmnth_firstday

And the second part of the condition

( ( datefrom BETWEEN p_x_setlmnth_firstday AND p_x_setlmnth_lastday ) OR ( datefrom LE p_x_setlmnth_firstday ) )

can be easily reduced to

datefrom LE p_x_setlmnth_lastday

This is a simple "date interval intersection" that is often used in SAP queries.

Finally your select will be:

SELECT logikzw profrole profile
    FROM eprofass
    INTO TABLE git_eprofass
    WHERE profrole EQ 'ESOG'   AND
        dateto GE p_x_setlmnth_firstday   AND

datefrom LE p_x_setlmnth_lastday.

When you then have an index with fields "profrole", "dateto", "datefrom" (in this order), then your select should have more or less optimal performance.

Regards,

Yuri

Re: Performance increase

Former Member — Thu, 24 May 2012 06:53:37 GMT

Please replay if there is any alternative way to achieve select condition i have ementioned.