Skip to Content
author's profile photo Former Member
Former Member

Range: Initial is better?

Hello guys!!

Please, look this two examples about performance:

Example 1:

SELECT bukrs belnr gjahr blart budat

FROM bkpf

INTO TABLE bkpf_int

WHERE bukrs EQ p_bukrs

AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')

AND budat = bkpf_wa-budat.

Example 2:

RANGES: r_bstat FOR bkpf-bstat.

SELECT bukrs belnr gjahr blart budat

FROM bkpf

INTO TABLE bkpf_int

WHERE bukrs EQ p_bukrs

AND bstat IN r_bstat "Range initial, without values.

AND budat = bkpf_wa-budat.

Question:

What is better?? What is faster?

Tanks!

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 15, 2008 at 01:29 PM

    Ranges are obselete in ECC 6.0 , so use IN ( like the 1st ). You can check the performance in se30 .

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 15, 2008 at 01:43 PM

    There should be no difference. You can check this by running both multiple times or by looking at the EXPLAIN in ST05.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 15, 2008 at 02:41 PM

    Your question

    What is better?? What is faster?

    has very little meaning when talking about performance tuning.

    Performance will vary depending on a number of factors - for example what database?, what version?, what optimisation method?, how many records in the table?, is the database on the same machine as the application server? or are they on different architectures even?, what cacheing / buffering is there?, are the statistics up to date?, etc.

    Tools such as ST05 and SE30 will allow you to look at how each is performing in your environment. Look at the execution plan the optimiser picks for the SQL in each case - is it using indexes? is it the same one in each case? is it just doing a table scan because there are not many records? Also look at things like how many fetches there are from the database server to the application server, and are these efficient with each returning a number of records appropriate to your architecture?. Check articles in the WIKI or in Blogs for lots of detailed hints on what to look for - but dont expect that everything that helps someone else will work in your environment in the same way.

    While it often possible to say that a particular SQL statement will perform badly - eg not using any indexed fields in the where block, to find what is optimum for a particular program on a particular system it is normally necessary to do the detailed analysis work - first in the development system to get the basics correct, but then often in the QA or Production (copy) system when it turns out that it performs differently with a different data volume or for some other reason.

    I would suggest that in your examples, in the second case with the empty ranges table, it is possible that SAP will not even mention the BSTAT field in the SQL it generates and passes to the database - which will possibly result in a different index being used giving a faster or slower read. Dont know for sure - have never looked at how SAP handles empty ranges.

    Andrew

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 16, 2008 at 10:08 AM

    The second selection is equivalent to :

    SELECT bukrs belnr gjahr blart budat

    FROM bkpf

    INTO TABLE bkpf_int

    WHERE bukrs EQ p_bukrs

    AND budat = wa_bkpf-budat.

    As in the 2 cases, the database will use index BKPF~2 (on fields BUKRS / BSTAT / BUDAT), we can think that the first solution (with BSTAT in (' ', 'A', ...)) will be better than the second one.

    But in fact, specifying a lot of different BSTAT forces the database to read more data from the index (it has to read the field BSTAT). Furthermore when the table is not very large, specifying more conditions can force the database to read a lot of times the same block of memory.

    That's why specifying this field is useful mainly when it is on rarely used values but not when specifying all existing values.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 23, 2008 at 06:22 AM

    Hi,

    Always better to restrict the result set as much as possible.

    • You don't restrict if you give all possible values to the IN LIST (1st select)

    i.e. The IN LIST will be transformed to OR statements by the database (makes the 1st a more complex query and possibly makes it more difficult to get a good execution plan)

    • You don't restrict if you give no IN LIST at all (2nd select)

    it omits the IN LIST and leaves only a limited choice of execution plans for the optimizer

    Then it's up to the database optimizer what execution path he's choosing (cost based otpimization: depends on data volume , your table and index statistics) or rule optimization.

    In this case it possibly would come out with the same execution plan.

    There is NEVER a general rule if you do this or avoid that it will be better for performance.

    Sometimes IN is the key to survive , sometimes it will kill your application response time,

    sometimes it makes no difference at all.

    Sure there are common pitfalls to be avoided or positively speaking:

    • know your database you are running on

    • Benchmark your different statements.

    • use ST05 and look HOW the SQL is processed by the database

    • take the needed actions to improve (SELECT or the data model i.e. meaningful indexing scheme)

    Bye

    yk

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.