SELECT statement performance in ABAP compared to SE16N

[Amitesh]

We have one custom program which has been running fine. Now, we have added following code and program is taking 5-7 minutes compared to 8-10 seconds.

    SELECT ACCVOUCHERNO EWBNUMBER FROM ZDIGI_OOWARD_H
    INTO TABLE gt_zdigi_ooward_h
    FOR ALL ENTRIES IN gt_vbrk
    WHERE ACCVOUCHERNO = gt_vbrk-vbeln.

Number of records  in gt_vbrk is approx. 2000 (rows and 2 columns) and using gt_vbrk-vbeln values in SE16N is taking max. 15 seconds. Also, ACCVOUCHERNO is key field in custom table along with 4 more primary keys. Custom table ZDIGI_OOWARD_H has approx 1.4 M records.

Looking for help to understand the reason for such delay in select statement.

Edit : We are on ECC6.0 and Oracle 12C DB.

[Sandra_Rossi]

Ask your database administrator to look at the execution plan to see what the problem is.

Thanks for indicating the native SQL statement, it deserves you mention it inside your question:

Where for FAE -  "MANDT"=:A0 AND "ACCVOUCHERNO" IN (:A1,:A2,:A3,:A4,:A5)
---------------------------------------------------------------
Where for SE16N - "MANDT"=:A0 AND ("ACCVOUCHERNO"=:A1 OR "ACCVOUCHERNO"=:A2 OR "ACCVOUCHERNO"=
:A3 OR "ACCVOUCHERNO"=:A4 OR "ACCVOUCHERNO"=:A5 OR "ACCVOUCHERNO"=:A6 OR
"ACCVOUCHERNO"=:A7 OR "ACCVOUCHERNO"=:A8 OR "ACCVOUCHERNO"=:A9 OR
........
"ACCVOUCHERNO"=:A199 OR "ACCVOUCHERNO"=:A200) AND ROWNUM <=:A201

Still you didn't indicate the execution plan.

You should have the same data type in both VBAK-VBELN and ZDIGI_OOWARD_H-ACCVOUCHERNO, i.e. 10 characters.

Look at SAP notes for Oracle performance.

You have FAE hints to force the use of OR instead of IN (refer to ABAP documentation and SAP notes).

SELECT * 
    FROM [..] 
    INTO [..]
    FOR ALL ENTRIES IN [..] 
    WHERE [..]
    %_HINTS ORACLE '&prefer_in_itab_opt=0&&prefer_union_all=0&'.

NB: also look at Oracle patches.

[Amitesh]

Hi Sandra,

Thanks for your valuable input. Hints suggested by you was going for dump and I have never used hints, so did not try much. Just read a bit of documentation and moved to alternative.

Join instead of FAE worked.

[Sandra_Rossi]

What can I say if you don't explain what dump you get, with which code and on which line? Also, if the runtime error happened after the SELECT, did you make sure that the SQL query was transformed into OR?

[raymond_giuseppi]

Which error raised the dump

Read also

• FAQ such as 772497 - FAQ: Oracle Hints and 48230 - Parameterization of SELECT ... FOR ALL ENTRIES statement 
• Look for notes such as 2052287 - FOR ALL ENTRIES %_HINTS ORACLE "&prefer_join 1&" ORA-904 for your version
• Look in forum for: st05 display execution plan 

Also, as you are on Oracle and not HANA,

• Is EWBNUMBER the second key of primary key, just after client/mandt
• Are statistics up to date online help on Oracle Update Statistics 

[BaerbelWinkler]

Hi Amitesh,

you write "Number of records  in gt_vbrk is approx. 2000". Is this what you expect to be in the table or have you verified it via debugging? I'm just asking as it is fairly easy to forget to check if the table used in the FOR ALL ENTRIES construct actually contains any data and your code snippet doesn't show if a check is actually there or not.

And, is EWBNUMBER also a key-field in table ZDIGI_OOWARD_H or just one of the other fields? If the latter, than there could also be a big difference in performance depending on whether or not you are on a HANA database, given the size of the table.

Hope this helps!

Cheers

Bärbel

[Amitesh]

Hi Barbel,

"Number of records  in gt_vbrk is approx. 2000" is checked during debugging (2167 exact number in one instance).

Table ZDIGI_OOWARD_H  has 4 key fields and EWBNUMBER is one of these 4 key fields. 

If it was not a key field then we might have thought of using secondary index but we are not using any non key field in where clause so we are stuck and looking for possible reason from the expert.

Thanks.

[raymond_giuseppi]

So you have a performance gap between

• SE16N with a value range
• FOR ALL ENTRIES with the same values

Did you

• Perform a SQL trace to analyze it?
• Check some parameters such as rsdb/max_blocking_factor and rsdb/max_in_blocking_factor parameters

[Amitesh]

Hi Raymond,

We have performance gap between SE16N and FAE with same values.

SQL trace shows expected high duration for each FETCH operation for FAE. While SE16N FETCH operations have lower duration for each Fetch (1200 vs 880,000) operation. 

Where for FAE -  "MANDT"=:A0 AND "ACCVOUCHERNO" IN (:A1,:A2,:A3,:A4,:A5)

Where for SE16N - "MANDT"=:A0 AND ("ACCVOUCHERNO"=:A1 OR "ACCVOUCHERNO"=:A2 OR "ACCVOUCHERNO"=
:A3 OR "ACCVOUCHERNO"=:A4 OR "ACCVOUCHERNO"=:A5 OR "ACCVOUCHERNO"=:A6 OR
"ACCVOUCHERNO"=:A7 OR "ACCVOUCHERNO"=:A8 OR "ACCVOUCHERNO"=:A9 OR
........
"ACCVOUCHERNO"=:A199 OR "ACCVOUCHERNO"=:A200) AND ROWNUM <=:A201

Value for rsdb/max_blocking_factor is maintained as 5.

We are using Oracle 12c DB with EHP5 (ECC 6.0).

Thanks.

[ptrck]

What value is set to 'Max. Number of Hits' in SE16N? Did you try it without a limit?

[Amitesh]

Hi Ptrck,

I had tried with 5000 limit and now I tried without limit and not much difference observed. Without limit it took 2-3 seconds more (it could be server load or any other reason as well).

[raymond_giuseppi]

So, with rsdb/max_blocking_factor = 5, the FAE is converted into multiple ranges of 5 records called in a loop, the performance gap is not surprising.

[keremkoseoglu]

In terms of performance, "For all entries" is usually evil.

If you are on Hana, try this instead:

SELECT 
  FROM gt_vbrk AS _vbrk
       INNER JOIN ZDIGI_OOWARD_H as _z ON _z~ACCVOUCHERNO = _vbrk~vbeln
  FIELDS _z~ACCVOUCHERNO, _z~EWBNUMBER 
  INTO TABLE gt_zdigi_ooward_h.

[Amitesh]

Hi Keremkoseoglu,

We are on ECC 6.0 and Oracle 12c DB. It does not allow join with internal table.

Used range for vbeln (from likp table - precondition) in where

    SELECT e~ACCVOUCHERNO  e~EWBNUMBER v~fksto
      FROM ZDIGI_OOWARD_H as e
      INNER JOIN vbrk as v
      on e~ACCVOUCHERNO = v~vbeln
      into TABLE gt_zdigi_ooward_h
      WHERE v~vbeln in r_vbeln.
delete gt_zdigi_ooward_h where fksto NE 'c_stat'.

 Thanks. 

[clemenssss]

SE16N works with ranges for the selection. Put your GT_VBRK-vbeln into a range (SIGN = 'I', OPTION = 'EQ', LOW = GT_VBRK-vbeln).

If it dumps due to too big SELECT statement (ORACLE may be 32kBytes), do as SE16N does: Split in more than one SELECT using partial range tables appending results table.

Or go for column-oriented HANA DB where everything is faster anyway ad the select statement size is limited at some gigabytes.

Your  ZDIGI_OOWARD_H may also need a database statistics refresh - look at the size category and talk to basis people.

Regards, Clemens