Accelerating custom SAP ECC transactions with HANA – Part 2

As introduced by the previous blog post Accelerating custom SAP ECC transactions with HANA – Part 1,  let’s imagine we have a custom “slow” report in SAP ECC, specially developed for the simulation of discounts, and having the simulation routine to be applied to many millions or records of the well known database table “VBFA” (Sales Document Flow).

Tipical size of table VBFA is ~150M of records, with reasonable growth rate of 30M records per year.

Assuming that our custom report works selecting a specifc calendar year, we have our database select fetching ~30M of records and our simulation routine that will compute each one of ~30M of records.

The ABAP code looks like this:

* DISCOUNT  SIMULATION DATA SELECTION   select rfwrt waers rfmng from vbfa into table tb_vbfa_opensql where          erdat > '20100101' and          erdat < '20101231' and          waers <> ''.    * DISCOUNT  SIMULATION ROUTINE     loop at tb_vbfa_opensql.       if tb_vbfa_opensql-rfmng >= 100.         tb_vbfa-rfwrt = tb_vbfa_opensql-rfwrt * ( 90 / 100 ).       else.         tb_vbfa-rfwrt = tb_vbfa_opensql-rfwrt.       endif.       tb_vbfa-waers = tb_vbfa_opensql-waers.       collect tb_vbfa.     endloop.

If we run our simulation report “as is” with standard underlying RDBMS system we have execution time like this:

Let’s now to try the HANA Application Accelerator Add On (aka SWT2DB, SAP Note 1696402).

We can use report RDA_MAINTAIN to create our scenario in order to switch to HANA, and without any ABAP modification, we will get a similar performance:

With the use of SWT2DB add-on the performances shows a DB Query access time dropped from ~900 sec to ~200 sec, and the time spent by ABAP executing calculations (business logic) still ~26 seconds.

Let’s try now the “ABAP to SQLScript” solution, where it’s directly HANA to execute all the business logic. The ABAP code will be something like this:

    try.         con_ref = cl_sql_connection=>get_connection( con_name ).         stmt_ref = con_ref->create_statement( ).         clear result_tab.         get time stamp field time_stamp.         clear res_ref.         clear d_ref.         concatenate sqlstmt ' select                                                                                                   ' into  sqlstmt.         concatenate sqlstmt '   sum(case when(rfmng >= 100) then (rfwrt * 0.90) else (rfwrt) end), ' into  sqlstmt.         concatenate sqlstmt '   waers                                                                                                ' into  sqlstmt.         concatenate sqlstmt ' from "TECHEDGE"."VBFA"                                                                ' into  sqlstmt.         concatenate sqlstmt ' where erdat > ''20100101''                                                                  ' into  sqlstmt.         concatenate sqlstmt ' and erdat < ''20101231''                                                                      ' into  sqlstmt.         concatenate sqlstmt ' and waers != ''''                                                                                   ' into  sqlstmt.         concatenate sqlstmt ' group by waers                                                                                   ' into  sqlstmt.         get time stamp field time_stamp.                 try.             res_ref = stmt_ref->execute_query( sqlstmt ).             time_stamp_old = time_stamp. get time stamp field time_stamp.             time_stamp_delta = time_stamp - time_stamp_old.             write:/ 'HANA (calculation engine) query execution time:', time_stamp_delta, 'seconds'. * Set output table             get reference of result_tab into d_ref.             res_ref->set_param_table( d_ref ). * Get the complete result set in the internal table             output = res_ref->next_package( ).           catch cx_sql_exception into sqlerr_ref.             perform handle_sql_exception using sqlerr_ref.         endtry.

And you can expect you will get a similar performance:

The complete execution time (now we have data selection + business logic calculations at the same time) is about 1,5 sec.

Thanks to the full exploitation of HANA, the improvement we get is really amazing.

Of course in normal cases the requirement is to access different tables, with joins and complicated business logic, but with appropriately structured SQL Script the performance increase can be even more surprising.

A special thanks to my colleague Laura Copot and Alessandro Lavazzi.

With them  I could ascertain what it means to get an “appropriately structured SQL Script”. ????