Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
Former Member

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:

  select rfwrt waers rfmng from vbfa into table tb_vbfa_opensql where
         erdat > '20100101' and
         erdat < '20101231' and
         waers <> ''.
    loop at tb_vbfa_opensql.
      if tb_vbfa_opensql-rfmng >= 100.
        tb_vbfa-rfwrt = tb_vbfa_opensql-rfwrt * ( 90 / 100 ).
        tb_vbfa-rfwrt = tb_vbfa_opensql-rfwrt.
      tb_vbfa-waers = tb_vbfa_opensql-waers.
      collect tb_vbfa.

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

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:

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:

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

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". :smile:

1 Comment
Labels in this area