topic Re: ranges in Application Development and Automation Discussions

ranges

Former Member — Thu, 04 Oct 2007 09:21:24 GMT

Hi,

am using ranges in below select..

is there any performance issue..can u suggest a beeter way other than ranges..

SELECT bukrs belnr budat blart awkey

INTO TABLE ibkpf

FROM bkpf

WHERE bukrs = p_bukrs

AND gjahr = gjahr

AND monat IN s_monat

AND blart IN r_blart

AND budat IN s_date.

Re: ranges

Former Member — Thu, 04 Oct 2007 09:24:12 GMT

hi,

no there is no performance issue at all.

you are doing it rightly.

only remember place the fields in select query as they are in database table i.i first primary keys and then other if they are on selection screen.

Re: ranges

former_member485052 — Thu, 04 Oct 2007 11:43:03 GMT

Hi Tuborg,

Nice, But it is always better to warn / Inform the user when Empty date ranges are used Particularly with Date on Tables like BKPF, MKPF, CDHDR, .... tables. Better to Schedule the Program in Backgroud.

Volume of Data will be very large..

your program need not fail because of time out error.

Regards,

Sureshkumar.AL

Re: ranges

Former Member — Thu, 04 Oct 2007 11:48:15 GMT

Hi,

No performance issues, however to avaoid empty date fields assed to the select query, make them atory in the selection screen.

Otherwise if date is not given it will fetch large number of data

Re: ranges

Former Member — Thu, 04 Oct 2007 13:20:41 GMT

Yes there is. Your SELECT will try to use either index 2 or 3 depending on your database statistics, but won't be able to do it effectively because you are not using BSTAT in the WHERE clause. It may seem counter-intuitive, but simply adding all values of that field to the WHERE may speed it up considerably:

SELECT bukrs belnr budat blart awkey
  INTO TABLE ibkpf
  FROM bkpf
  WHERE bukrs = p_bukrs
    AND gjahr = gjahr
    AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')    "<=======
    AND monat IN s_monat
    AND blart IN r_blart
    AND budat IN s_date.

As the others have suggested, make sure that either r_blart or s_date is not empty and does not contain any 'NE'

Rob