topic Re: Select performance issue in Application Development and Automation Discussions

Select performance issue

Former Member — Fri, 21 Oct 2011 08:53:52 GMT

SELECT vbap~matnr

vbak~vbeln

vbak~vdatu

vbap~kwmeng

vbap~pstyv

INTO CORRESPONDING FIELDS OF TABLE lt_so_required

FROM vbak JOIN vbap

ON vbak_{vbeln = vbap}vbeln

FOR ALL entries IN it_matnr

WHERE vbap~matnr = it_matnr-matnr

AND vbap~werks IN is_sel_scr-werks

AND vbak~vdatu BETWEEN is_sel_scr-d_start

AND is_sel_scr-d_end

AND vbak~auart IN is_sel_scr-auart

AND vbak~vkorg = is_sel_scr-vkorg

AND vbak~vtweg IN is_sel_scr-vtweg

AND vbak~spart IN is_sel_scr-spart

AND ( vbap~pstyv IN is_sel_scr-pstyvs OR

vbap~pstyv IN is_sel_scr-pstyvo ).

Could you please point out the way of improving this select?

Re: Select performance issue

yuri_ziryukin — Fri, 21 Oct 2011 09:13:17 GMT


SELECT vbap~matnr
>        vbak~vbeln
>        vbak~vdatu
>        vbap~kwmeng
>        vbap~pstyv
>        INTO CORRESPONDING FIELDS OF TABLE lt_so_required
>        FROM vbak JOIN vbap
>          ON vbak_{vbeln = vbap}vbeln
>        FOR ALL entries IN it_matnr
>        WHERE vbap~matnr = it_matnr-matnr
>          AND vbap~werks IN is_sel_scr-werks
>          AND vbak~vdatu BETWEEN is_sel_scr-d_start
>                            AND is_sel_scr-d_end
>          AND vbak~auart IN is_sel_scr-auart
>          AND vbak~vkorg = is_sel_scr-vkorg
>          AND vbak~vtweg IN is_sel_scr-vtweg
>          AND vbak~spart IN is_sel_scr-spart
>          AND ( vbap~pstyv IN is_sel_scr-pstyvs OR
>                vbap~pstyv IN is_sel_scr-pstyvo ).
> 
> Could you please point out the way of improving this select?

Hello Wojciech,

please use the "index" table VAPMA for your selection and join it with VBAP.

The only field from VBAK I could not find in VAPMA is the VDATU. Please check if you can replace it with another field like AUDAT or some date field from VBAP.

Then your select would look like:

>SELECT vapma~matnr

> vapma~vbeln

> vapma~audat <---- see my comment above about the date

> vbap~kwmeng

> vbap~pstyv

> INTO CORRESPONDING FIELDS OF TABLE lt_so_required

> FROM vapma INNER JOIN vbap

> ON vapma_{vbeln = vbap}vbeln AND vapma_{posnr = vbap}posnr

> FOR ALL entries IN it_matnr

> WHERE vapma~matnr = it_matnr-matnr

> AND vapma~werks IN is_sel_scr-werks

> AND vapma~audat BETWEEN is_sel_scr-d_start <---- see my comment about the date

> AND is_sel_scr-d_end

> AND vapma~auart IN is_sel_scr-auart

> AND vapma~vkorg = is_sel_scr-vkorg

> AND vapma~vtweg IN is_sel_scr-vtweg

> AND vapma~spart IN is_sel_scr-spart

> AND ( vbap~pstyv IN is_sel_scr-pstyvs OR

> vbap~pstyv IN is_sel_scr-pstyvo ).

Better results will be achieved if you also pass the value of VAPMA~TRVOG (Transaction Group) in the select.

Regards,

Yuri

Re: Select performance issue

Former Member — Fri, 21 Oct 2011 10:50:34 GMT

unfortunately the date field is needed 😕

is there any sense making the index on the tables?

Re: Select performance issue

yuri_ziryukin — Fri, 21 Oct 2011 12:34:40 GMT


unfortunately the date field is needed :/
> 
> is there any sense making the index on the tables?

Hello Wojciech,

if this field is needed, then include VBAK as the third table in the join.

It might be beneficial to create a secondary index for VAPMA with the fields that are always specified in your selection. Due to the usage of select options I cannot see what else except material number is always (or very often) passed to the SELECT.

Regards,

Yuri

Re: Select performance issue

mayankmrai — Fri, 28 Oct 2011 04:50:01 GMT

Hi,

Better use for all entries in select query instead of inner join..

Re: Select performance issue

yuri_ziryukin — Fri, 28 Oct 2011 09:14:32 GMT


Hi,
> 
> Better use for all entries in select query instead of inner join..

Better read corresponding threads with the discussion FAE vs JOIN and do not give useless and wrong advices.

Re: Select performance issue

naveen_inuganti2 — Fri, 28 Oct 2011 09:19:34 GMT

Hi,

Don't use 'Into Corresponding Fields Of' addition. Instead of that, you can declare structure(ref. internal table) with fields in same order they appear in SELECT query.

Don't use BETWEEN statement, instead of that you might want to look at other options like ranges table etc.,

Never go for OR statement because this causes double search on database. it's better to get all records instead and process what ever are required.

Index Utility:

Ex: F1, F2, F3, F4 are fields and followed same order in index creation -->

F1, F2, F3 = using this in SELECT query triggers index

F2, F3, F4 = Using this in SELECT triggers index

F1, F2, F3, F4 = Also triggers Index

F1, F3, F4 = Never triggers index as was broken at F2

Try to add following oracle hint at the end of the SELECT query - This speeds up FOR ALL ENTRIES query as we are passing blocking factor as 10 and 100. NOTE: This does not apply if amount of data is less.

%_hints oracle '&min_in_blocking_factor 10&&max_in_blocking_factor 100&&min_blocking_factor 10&&max_blocking_factor 100&'.

Thanks,

Naveen Inuganti

Re: Select performance issue

Former Member — Fri, 28 Oct 2011 09:23:18 GMT

How much entries does your IT_MATNR host?

If it´s around thousand or less you could make yourself a range table of MATNR for instance.

Re: Select performance issue

yuri_ziryukin — Mon, 31 Oct 2011 09:14:08 GMT


Hi,
> 
> Don't use 'Into Corresponding Fields Of' addition. Instead of that, you can declare structure(ref. internal table) with fields in same order they appear in SELECT query.
> 
> Don't use BETWEEN statement, instead of that you might want to look at other options like ranges table etc.,
> 
> Never go for OR statement because this causes double search on database. it's better to get all records instead and process what ever are required.
> 
> Index Utility:
> Ex: F1, F2, F3, F4 are fields and followed same order in index creation -->
> F1, F2, F3 = using this in SELECT query triggers index
> F2, F3, F4 = Using this in SELECT triggers index
> F1, F2, F3, F4 = Also triggers Index
> F1, F3, F4 = Never triggers index as was broken at F2
> 
> 
> Try to add following oracle hint at the end of the SELECT query - This speeds up FOR ALL ENTRIES query as we are passing blocking factor as 10 and 100. NOTE: This does not apply if amount of data is less.
> %_hints oracle '&min_in_blocking_factor 10&&max_in_blocking_factor 100&&min_blocking_factor 10&&max_blocking_factor 100&'.
> 
> Thanks,
> Naveen Inuganti

1. "into corresponding fields" overhead is so little, that it should not be considered. So your advice number 1 is useless.

2. What is a difference between "BETWEEN" statement and a range like "I BT XXX YYY"? Please support your statement with a link to an explanation.

3. Second advice of not using OR statement is again wrong. It is not better to get all data from the DB and filter in ABAP. This has been discussed in many threads already.

4. I have no idea what "index utility" is mentioned, but the text is simply wrong and confusing.

Four useless/wrong advices in one post is too much for me...

Re: Select performance issue

naveen_inuganti2 — Mon, 31 Oct 2011 11:27:03 GMT

Yuri,

Thanks for going through all my options. Also please try it in editor once and come back here.

I have tried all of them, not my assumptions.

Thanks,

Naveen Inuganti

Re: Select performance issue

Former Member — Mon, 31 Oct 2011 13:23:50 GMT

Naveen - if you go through old forum posts, you'll find that Yuri is correct.

And I have tried all of your options

Rob

Re: Select performance issue

naveen_inuganti2 — Mon, 31 Oct 2011 14:53:34 GMT

Rob,

As Yuri said, every thing is incorrect... let me take few cases:

On OR Statement

In some cases, it can be beneficial to simplify the WHERE conditions used on a LOOP iterator. For example, if you have several conditions that check the same field against a set of values using OR operators, it can be beneficial to place the conditions in a RANGE or SELECT-OPTION object and change the WHERE clause to: WHERE field IN [range] | [select-option]. The IN can be processed faster than several conditional statements joined by an OR (assuming you are using the same field as an operator). Example:

GOOD: u2026 WHERE field1 IN range1.

NOT AS GOOD: u2026 WHERE field1 = v1 OR field1 = v2 OR field1 = v3 OR field1 = v4.

On For All Entries

When using the FOR ALL ENTRIES clause, performance can be improved by increasing the blocking factor. Since the FOR ALL ENTRIES clause essentially breaks down a table of records to be read into individual requests, the blocking factor controls how many of those records are grouped into a single request to the database. The default blocking factor is 5. For example, if the FOR ALL ENTRIES table contains 100 entries and the default blocking factor is used, then 20 separate requests for 5 records each will be sent to the database. If the blocking factor is increased to 100, then only 1 request would be sent to the database to retrieve all 100 records. This reduces the overhead needed to retrieve the data. Be careful not to increase the blocking factor too high as it increases the length of the query and there are maximum limitations in the database that will generate a short-dump if exceeded. The following Oracle u201Chintu201D can be added to queries to increase the blocking factor to 100 for that specific query only: %_HINTS ORACLE '&min_in_blocking_factor 10&&max_in_blocking_factor 100&&min_blocking_factor 10&&max_blocking_factor 100&'. Note that this hint will only optimize database queries when the FOR ALL ENTRIES clause is used and there are at least 100 entries expected in the result set

On Index

Whenever possible, provide all fields in the primary key or index in the WHERE clause without gaps. Itu2019s OK to leave a field off at the end of the primary key or index, but gaps within the primary key or index should be avoided. For example, if an index is comprised of f1, f2, f3 and f4, then the following applies:

u2022 WHERE f1= v1 AND f2 = v2 AND f3 = v3 AND f4 = v4 OK

u2022 WHERE f1=v1 AND f2 = v2 AND f3 = v3 OK

u2022 WHERE f1 = v1 AND f3 = v3 Not recommended (gap)

Provide me URLs for thread where it has been proven that above is useless and incorrect!

Regards,

Naveen.I

Re: Select performance issue

Former Member — Mon, 31 Oct 2011 15:23:53 GMT

Please provide SAP documentation that shows this is correct.

Rob

Re: Select performance issue

yuri_ziryukin — Wed, 02 Nov 2011 08:35:29 GMT

Ok, Naveen, let's go...


> On OR Statement
> In some cases, it can be beneficial to simplify the WHERE conditions used on a LOOP iterator.  For example, if you have several conditions that check the same field against a set of values using OR operators, it can be beneficial to place the conditions in a RANGE or SELECT-OPTION object and change the WHERE clause to:  WHERE field IN [range] | [select-option].  The IN can be processed faster than several conditional statements joined by an OR (assuming you are using the same field as an operator).   Example:
> GOOD:  u2026 WHERE field1 IN range1.
> NOT AS GOOD: u2026 WHERE field1 = v1 OR field1 = v2 OR field1 = v3 OR field1 = v4.

This is absolutely the same. There is no single reason why IN list should be faster than OR. Wasted time for changing the code.


> On For All Entries
> When using the FOR ALL ENTRIES clause, performance can be improved by increasing the blocking factor.  Since the FOR ALL ENTRIES clause essentially breaks down a table of records to be read into individual requests, the blocking factor controls how many of those records are grouped into a single request to the database.  The default blocking factor is 5.  For example, if the FOR ALL ENTRIES table contains 100 entries and the default blocking factor is used, then 20 separate requests for 5 records each will be sent to the database.  If the blocking factor is increased to 100, then only 1 request would be sent to the database to retrieve all 100 records.  This reduces the overhead needed to retrieve the data.  Be careful not to increase the blocking factor too high as it increases the length of the query and there are maximum limitations in the database that will generate a short-dump if exceeded.  The following Oracle u201Chintu201D can be added to queries to increase the blocking factor to 100 for that specific query only:  %_HINTS ORACLE '&min_in_blocking_factor 10&&max_in_blocking_factor 100&&min_blocking_factor 10&&max_blocking_factor 100&'.  Note that this hint will only optimize database queries when the FOR ALL ENTRIES clause is used and there are at least 100 entries expected in the result set

If you carefully read my reply, I did not comment your statement regarding max_blocking_factor. Indeed it can help in many cases.


> On Index
> Whenever possible, provide all fields in the primary key or index in the WHERE clause without gaps.  Itu2019s OK to leave a field off at the end of the primary key or index, but gaps within the primary key or index should be avoided.  For example, if an index is comprised of f1, f2, f3 and f4, then the following applies:
> u2022	WHERE f1= v1 AND f2 = v2 AND f3 = v3 AND f4 = v4	OK
> u2022	WHERE f1=v1 AND f2 = v2 AND f3 = v3			OK
> u2022	WHERE f1 = v1 AND f3 = v3				Not recommended (gap)
>

Sure this is correct, but it's so obvious that almost nobody is mentioning this stuff in this forum anymore. It can be found in thousands of places in Internet. It's like common sense.

And well, there is a little difference to what you have said before. Let me remind you...


> F2, F3, F4 = Using this in SELECT triggers index

Don't you see the "gap" at field1? So your original "advice" was incorrect as I already mentioned.

And believe me, I am doing performance optimizations for many SAP customers for the last 7 years. If somebody tells me something new, I always ask for the technical explanation (because as the end of the day the customer will ask me why did I advise something like this?). Without the technical explanation why certain thing is faster than another one, I don't trust anyone.

Kind regards,

Yuri

Re: Select performance issue

Former Member — Wed, 02 Nov 2011 12:57:09 GMT

there was an old recommendation to change ORs into IN-list (no ranges but IN (a, b, ...) programmed. I do not know whether this is still valid.

>F1, F2, F3 = using this in SELECT query triggers index

>F2, F3, F4 = Using this in SELECT triggers index

>F1, F2, F3, F4 = Also triggers Index

>F1, F3, F4 = Never triggers index as was broken at F2

Only option 3 is correct, if the key is the primary key, because this is unique

All others depend on costs and available other indexes.

Option 2 is a misinterpretation of the Oracle skip scan: Works only if different values for f1 are small and only for Oracle.

Option 4 again assums low selectivity for F1, because it would work fine, if the selectivity of f1 is high.

Replacing joins by FAE was discussed often ... the solution is 'it depends'.

Changing blocking factor - either generally or with a hint - is not recommended. This should only be done, if it is absolutely clear, how the statement is used (size of table, size of driver can normally vary a lot)

Re: Select performance issue

volker_borowski2 — Wed, 02 Nov 2011 22:07:06 GMT

Hi,

this is the real fun with modern databases. They are so clever...

This is an example from my toy-around 11g babe:


SQL> select * from emp where empno=7369 or empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |    64 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    64 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMPNO"=7369 OR "EMPNO"=7900)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
        939  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

I feel the need to pad on it's back mumbeling "good boy".

So the DB decides for an inlist iteration if you provide OR clauses on the same column.

Since some time you can not even override this with a use_concat hint any more.

The hint is still there, but does not seem to work when the same column is "OR"ed.

So the statement is in fact not transformed to a different plan any more,

unless you are still using a very old version.

Volker

Re: Select performance issue

yuri_ziryukin — Thu, 03 Nov 2011 08:03:54 GMT

Excellent, thanks for the confirmation, Volker.