Skip to Content

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:

Pic1-Cut.png

With around 900 seconds spent in fetching the records (that is a lot of time and probably can in some measure be reduced with some kind of optimization), and with a rather significant 26 seconds spent by ABAP engine executing calculations the simulation (business logic).

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:

Pic2-Cut.png

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:

Pic3-Cut.png

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”. 🙂

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply