With SAP Netweaver AS ABAP 7.4, I thought of a simple ABAP program to compare the performance between ABAP program consisting of classic SQL(multiple joins & currency conversion) against the ABAP program calling HANA calculation view directly (classic SQL with joins & currency conversion logic converted into HANA calculation view).
- ABAP with SQL embedded – SQL query with multiple joins & currency conversion in ABAP program.
- ABAP program calling HANA calculation view directly a.k.a code push down to HANA DB.
You can manage ABAP program, modeling & ABAP profiling all in one place with SAP HANA studio. You need to install additional ABAP development plugins for eclipse and I used SAP HANA Studio version: 1.0.58.
Below is the screen I captured while profiling ABAP program from HANA studio. ABAP program uses classic SQL to fetch data from BKPF, BSEG (multiple joins) and perform currency conversion using standard function module. Actual database time that it took to execute ~ 24 seconds.
From the image below
SQL statement navigated from hit list (Screen 3) shows more time consumed at DB during execution. SQL query from screen 1 is later converted into calculation view in HANA DB to improve the performance drastically.
Snapshot from ABAP profiling analysis done in HANA studio with database time: ~ 24seconds
Complete Hit list of ABAP program showing BKPF SQL fetch and currency conversion (Perform CONV_CC) which consumed most of the time. Both the SQL statement and currency conversion is pushed down to calculation view for performance boost.
Currency conversion code inside – Perform CONV_CC.
ABAP code optimization using HANA 24 seconds to 2 seconds:
SQL query & currency conversion is converted into HANA calculation view and called from SQL query as shown below: ZBC_BSEG_DOC is the dictionary view created from HANA calculation view.
HANA Calculation view:
HANA views can be accessed by standard SQL With ABAP 7.40 they are natively supported in ABAP
- Mapping to DDIC types possible
ABAP profiling & performance after code push down to HANA
ABAP code optimized for HANA. All SQL joins & currency conversion pushed down to HANA DB as calculation view CA_BSEG_DOC.
Proxy ABAP dictionary view generated from HANA calculation view so it can be used in ABAP SQL.
ABAP profiling snapshot showing approx. 2 seconds spent in database during execution compared to 24 seconds with classic SQL.
HANA calculation view inside HANA DB.
Hit list from ABAP profiling.