Performance increase

[Former Member]

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

[former_member282968]

This message was moderated.

[Former Member]

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

[yuri_ziryukin]

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