Compare performance between SELECT FOR ALL ENTRIES and AMDP

Even with S4/HANA, we still need to use the SELECT FOR ALL ENTRIES statement to join data in ABAP and data in the database as many legacy calculation logics are still in ABAP. This document is to compare the performance of SELECT FOR ALL ENTRIES statement and other possible replacements in SAP S4/HANA systems.

In this test, we use the function module BAPI_SBOOK_GETLIST to retrieve the booking list and access the database table SBOOK to read details of each booking.

Test system: S4/HANA 1809 on HANA 2.0

Test #1: SELECT FOR ALL ENTRIES with Fast Data Access

In the test system, the FDA (Fast Data Access) feature is turned on. Therefore, the rows in the internal table LT_BOOKINGLIST are sent to the HANA database in an array.

V_CARRID = 'SQ'.
V_CONNID = '0325'.
V_DATUM = '20100707'.

CALL FUNCTION 'BAPI_SBOOK_GETLIST'
  EXPORTING
    AIRLINECARRIER   = V_CARRID
    CONNECTIONNUMBER = V_CONNID
    DATEOFFLIGHT     = V_DATUM
    CUSTOMERNUMBER   = V_CUSTOMID
    ORDERDATE        = V_ORDERDATE
  TABLES
    BOOKINGLIST      = LT_BOOKINGLIST.

SELECT *
  FROM SBOOK
  INTO TABLE @DATA(LT_SBOOK)
  FOR ALL ENTRIES IN @LT_BOOKINGLIST
  WHERE CARRID = @LT_BOOKINGLIST-CARRID
    AND CONNID = @LT_BOOKINGLIST-CONNID
    AND FLDATE = @LT_BOOKINGLIST-FLDATE
    AND BOOKID = @LT_BOOKINGLIST-BOOKID.

Native SQL statement

SQL Trace shows the SELECT FOR ALL ENTRIES statement with FDA takes about 6ms to send the array and to receive the result set from the database.) )

Test #2: SELECT FOR ALL ENTRIES without Fast Data Access

In this test, the hint ‘&prefer_join_with_fda 0&’ is used to simulate the system FDA is disabled. As FDA is off, the data in the internal table is sent to the database as where conditions with OR operators. As the max blocking factor is 50 is default in the HANA database, 50 rows are passed to the database at a time.

SELECT *
  FROM SBOOK
  INTO TABLE @DATA(LT_SBOOK)
  FOR ALL ENTRIES IN @LT_BOOKINGLIST
  WHERE CARRID = @LT_BOOKINGLIST-CARRID
    AND CONNID = @LT_BOOKINGLIST-CONNID
    AND FLDATE = @LT_BOOKINGLIST-FLDATE
    AND BOOKID = @LT_BOOKINGLIST-BOOKID
  %_HINTS HDB '&prefer_join_with_fda 0&'.

Native SQL statement

SQL Trace shows the SELECT FOR ALL ENTRIES statement without FDA takes about 16.9ms to execute 9 native SQL statements (437 rows / 50 (value for the profile rsdb/max_blocking_factor) )

Test #3: ADMP (ABAP Managed Database Procedure)

In this test, ADMP is used to join the rows in the ABAP internal table with rows in the database table.  In the AMDP method, the MANDT track must be added in the where clause, and DISTINCT option needs to be added to the SELECT statement. This is to avoid duplicate rows in the result set (SELECT FOR ALL ENTRIES statement removes the duplicate rows automatically).

ZCL_MY_FIRST_AMDP=>READ_SBOOK(
                EXPORTING IT_BOOKS = LT_BOOKINGLIST
                IMPORTING ET_SBOOK = DATA(LT_SBOOK) ).

METHOD READ_SBOOK BY DATABASE PROCEDURE
                                  FOR HDB
                                  LANGUAGE SQLSCRIPT
                                  OPTIONS READ-ONLY
                                  USING SBOOK.

 ET_SBOOK = SELECT DISTINCT K.MANDT, K.CARRID, K.CONNID, K.FLDATE, 
                   K.BOOKID, K.CUSTOMID, K.CUSTTYPE, K.SMOKER, 
                   K.LUGGWEIGHT, K.WUNIT, K.INVOICE, K.CLASS, 
                   K.FORCURAM, K.FORCURKEY, K.LOCCURAM, K.LOCCURKEY, 
                   K.ORDER_DATE, K.COUNTER, K.AGENCYNUM, K.CANCELLED, 
                   K.RESERVED, K.PASSNAME, K.PASSFORM, K.PASSBIRTH
                FROM SBOOK K INNER JOIN :IT_BOOKS 
                     ON K.MANDT = SESSION_CONTEXT('CLIENT')
                     AND K.CARRID = :IT_BOOKS.CARRID
                     AND K.CONNID = :IT_BOOKS.CONNID
                     AND K.FLDATE = :IT_BOOKS.FLDATE
                     AND K.BOOKID = :IT_BOOKS.BOOKID;
ENDMETHOD.

Native SQL statement

SQL trace shows the ADMP takes about 24.6ms including two TRUNCATE statements and an INSERT statement to a temporary data object. It seems INSERT statement is used to send the rows in the internal table to the database.

Test #4: Join an internal table with DB tables

From ABAP 7.52, an internal table can be specified as a data source for OPEN SQL and it is also possible to join an internal table with DB tables. As it’s OPEN SQL, the MANDT field is automatically added in the generated native SQL statement. It’s still a good idea to add the DISTINCT option to avoid possible duplicate rows.

SELECT DISTINCT SK~CARRID, SK~CONNID, SK~FLDATE, SK~BOOKID, SK~CUSTOMID,                
         SK~CUSTTYPE, SK~SMOKER,SK~LUGGWEIGHT, SK~WUNIT, SK~INVOICE,                 
         SK~CLASS, SK~FORCURAM, SK~FORCURKEY, SK~LOCCURAM, SK~LOCCURKEY, 
         SK~ORDER_DATE, SK~COUNTER, SK~AGENCYNUM, SK~CANCELLED, 
         SK~RESERVED,  SK~PASSNAME, SK~PASSFORM,SK~PASSBIRTH       
  FROM SBOOK AS SK INNER JOIN @LT_BOOKINGLIST AS BLIST            
           ON SK~CARRID = BLIST~CARRID            
           AND SK~CONNID = BLIST~CONNID            
           AND SK~FLDATE = BLIST~FLDATE            
           AND SK~BOOKID = BLIST~BOOKID       
  INTO TABLE @DATA(LT_SBOOK).

Native SQL statement

SQL Trace shows the domestic table join takes about 6.8ms. It seems the way working in the behind scene is similar to that of the FDA (Fast Data Access).

One thing we need to make sure is that the FDA feature is turned on in the database, because, the internal table join with DB table can’t be used if the FDA feature is disabled by HANA DB parameter (fda_enabled = off or abap_itab_parameter = off). The screenshot below is the short dump for this error.

Summary

Large volume test (1 million rows in the internal table)

Conclusion

• SELECT FOR ALL ENTRIES with FDA (test #1) feature enabled shows the best performance
• Join an internal table with the DB tables (test #4) can be used only when the database allows the FDA. otherwise, the statement will be terminated with ABAP short dump
• AMDP does not show better performance than SELECT FOR ALL ENTRIES with FAE. However, ADMP can be used regardless of the FDA feature enablement. And, if there is a possibility to move some calculation logic down to the database, it would be the best option to replace the SELECT FOR ALL ENTRIES statement especially when the FDA feature is disabled in the system