topic Range: Initial is better? in Application Development and Automation Discussions

Range: Initial is better?

Former Member — Mon, 15 Sep 2008 13:26:42 GMT

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!

Re: Range: Initial is better?

Former Member — Mon, 15 Sep 2008 13:29:06 GMT

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

Re: Range: Initial is better?

JozsefSzikszai — Mon, 15 Sep 2008 13:36:50 GMT

Ranges are obselete in ECC 6.0 ...

you mean the statement RANGES is obsolate, but we can still freely define ranges with ... TYPE RANGE OF ...

back to the original question: the 2nd example proved to be faster in my test.

Re: Range: Initial is better?

Former Member — Mon, 15 Sep 2008 13:43:08 GMT

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

Rob

Re: Range: Initial is better?

Former Member — Mon, 15 Sep 2008 14:41:51 GMT

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

Re: Range: Initial is better?

Former Member — Tue, 16 Sep 2008 10:08:46 GMT

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.

Re: Range: Initial is better?

Former Member — Tue, 23 Sep 2008 06:22:57 GMT

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