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

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.

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.) )

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.

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) )

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).

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.

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.

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.

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

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

，物联网展会，返利商城，云服务器特价，物联网展会，云100