ABAP Development

SQL Script for ABAP Managed Database Procedures(AMDP)-Code pushdown for a better performance!

We all are aware of the term “code pushdown” in the SAP HANA database and how it helps us in improving the performance of the application.

When it comes to performance intensive applications say an analytical report, the bottleneck lies in moving the records between the database server and the application server. The time taken is directly proportional to the number of records moved between the database server and the application server.

We all are used the technique of fetching the records using the SAP HANA CDS views and make the other calculations/processing/filtration in the ABAP layer.

The idea here is to perform all the processing of records in the database layer, rather than moving the large amount of unprocessed records to the ABAP layer.

When it comes to SAP HANA CDS views , we face certain limitations in terms of processing the data the way we want. Examples are delete the adjacent duplicates or use of Order by clause. That’s when we think of SAP HANA Table function using ABAP Managed Database Procedures(AMDP) as a savior.

Since the SAP HANA Table functions are built using SQL Script they offer a lot of flexibility to code simple to complex logic

Here is a handy SQL Script guide for the basic operations those we perform in the ABAP layer in order to process the data the way we want

Please Note: Use the AMDP table functions only in places where you cannot use the SAP HANA CDS views. CDS views are preferred over AMDP table functions for the optimization and parallelization they offer.

Simple operations.

  1. Declare internal table inside AMDP class
  2. Declare an ABAP datatype in SQL script
  3. Delete adjacent duplicates
  4. Sort by column and pick the latest value
  5. Convert a delimited string to an internal table
  6. Apply filter to local table
  7. Calling AMDP methods with parameters
  8. Check if the Internal table is not initial
  9. Select client specific data inside the AMDP method
  10. Convert the rows to columns using “Case statement” ( Transposition )

Declare internal table inside AMDP class

Go to the AMDP class and declare the internal table in the public section. Here we can make use of the ABAP syntax and the ABAP datatypes. Declaring the global table types are helpful in calling the AMDP methods with return parameters.

class zcl_com_final definition
 public
 final
 create public.
  public section.
    interfaces if_amdp_marker_hdb.
    types: begin of ty_itab,
	     rownum        type int2,
             db_key        type /bobf/conf_key,
             prod_hr_id    type /dmf/hierarchy_id,
            creation_date  type dats,	     		       
end of ty_itab,
gt_itab type standard table of ty_itab with unique key primary_key components rownum db_key.

Declare ABAP data type inside the SQL script

Below is an example of how we can declare an ABAP specific data type inside the AMDP method using the SQP script

declare lv_timestamp   "$ABAP.type( TZNTSTMPS )";

Delete adjacent duplicates

“Delete adjacent duplicates” is a very common statement in ABAP. Below is the syntax for the same in SQL script. This statement deletes the adjacent duplicate records based on the field “db_key” from table lt_itab

Lt_itab_noduplicates =  SELECT * FROM (  select
                        row_number() over ( partition by db_key ORDER BY db_key ) as  rownum , * from
                        :lt_itab) where rownum = 1 ;

Sort by column and pick the latest value

This is one stellar operation that we cannot achieve with our traditional CDS views. This is one of the most useful statements when it comes to filtering of the unwanted records

The below statement picks the latest offer number for the given product group id.

lt_latestoffer = select * from ( select row_number() over ( partition by prod_hr_id
                 order by creation_date desc ) as  rownum , * from :lt_itab ) where rownum = 1 ;

Here is the sample data

DB_KEY Prod_hr_id  Date 
1 123 4/4/2021
123  4/5/2021 
123  4/6/2021 
456  4/7/2021 
456  4/8/2021 
456  4/9/2021 

Output:

DB_KEY Prod_hr_id  Date 
3 123 4/6/2021
6 456 4/9/2021

Convert a delimited string to an internal table

CDS views do not support a larger string operation. The string functions are not supported for the datatype “STRING” . The below chunk of code comes handy when we have to pass multiple values as a parameter to the table function and later split them and use them inside the AMDP method.

Assume the value in lv_string = ABC|DEF|GHI|JKL

split_values = SELECT substr_before(:lv_string,'|') single_val FROM dummy;
      SELECT substr_after(:lv_string,'|')  INTO lv_string FROM dummy;
         while( length(:lv_string) > 0 )
            DO
                 split_values = SELECT substr_before(:lv_string,'|') single_val FROM DUMMY
                     UNION
                  SELECT single_val FROM :split_values;
                  SELECT substr_after(:lv_string,'|') INTO lv_string FROM dummy;
            END while;
   itab = SELECT single_val AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES  where single_val <> ' ';

Itab :

OUTPUT_SPLIT
ABC 
DEF 
GHI 
JKL 

Apply filter to a local table

I have made this example with product group number but in real time this can be used to separate the process types or any particular group of data from the other

ITAB

DB_KEY Prod_hr_id  Date 
3 123 4/6/2021
6 456 4/9/2021

declare lc_filter  string  := '( PROD_HR_ID = ' || '''123''' || ' )';
itab_result = apply_filter ( :itab , :lc_filter );

ITAB_RESULT

DB_KEY Prod_hr_id  Date 
3 123 4/6/2021

Calling AMDP methods with parameters

We can have an AMDP method with import and export parameter. This helps in modularizing and reusing the code.

Declare the class method like this

I have declared it with one importing parameter and one exporting parameter. You can have multiple import and export parameters to support your programming logic.

public section.
class-methods:
      get_ofrmain
        importing
          value(p_adzone)  type  char255
        exporting
          value(et_ofrmain)   type gt_itab.

Calling get_ofrmain method inside another method ofr_adzone.

method ofr_adzone
      by database function
      for hdb
      language sqlscript
      options read-only
      using  zcl_com_final=>get_ofrmain.

call "ZCL_COM_FINAL=>GET_OFRMAIN"  ( P_ADZONE => :P_ADZONE ET_OFRMAIN => :ET_OFRMAIN );
LT_OFRMAIN = SELECT * FROM :ET_OFRMAIN;

Check if Internal table is not initial

This is one important statement in our ABAP programing model and the most frequently used statement

SELECT COUNT(*) INTO numrows FROM :LT_OFRMAIN;
   IF numrows > 0 then
// program logic
   END IF;

Select the client specific data

Its very important to select client specific data while working with database schemas. The below method selects client specific data from a Z table ZPRD_DEPT which is part of the schema SAP_S4HANA

method Prd_dept
      by database function
      for hdb
      language sqlscript
      options read-only.
    RETURN select _Prd.mandt  as clnt, _Prd.sfs_dept_num,_Prd.sfs_dept_desc
             from "SAP_S4HANA"."ZPRD_DEPT" as _Prd where _Prd.mandt = session_context('CLIENT');
  endmethod.

Convert the Rows to Columns using “Case Statement” ( Transposition )

This operation is not supported in the CDS when the given datatypes are of “STRING”. During such instances , instead of jumping into the ABAP layer , we can efficiently perform such operations using SQL Script in AMDP table functions.

ZPRD_ATTR

PRODHRID ATTRIBUTE ATTRIBUTEVALUE
123 0001 COLOR : YELLOW
123 0002 SIZE : 10 GRAMS
123  0003 TYPE : JELLY
456 0001 COLOR : BLUE
456  0002 SIZE : 500 GRAMS
456  0003 TYPE : CREAM

method get_prodatt
        by database function
        for hdb
        language sqlscript
        options read-only
        using zprd_attr.

    lt_att = select prodhrid,

             max (case
             when attribute = '0001' then
             cast(attributevalue as char( 255 ))
             end ) as ATTRIBUTEVALUE1,

            max (case
            when attribute = '0002' then
            cast(attributevalue as char( 255 ))
            end ) as ATTRIBUTEVALUE2,

            max (case
            when attribute = '0003' then
            cast(attributevalue as char( 255 ))
            end ) as ATTRIBUTEVALUE3

            from zprd _attr
            group by prodhrid;

return select  prodhrid , concat(  ATTRIBUTEVALUE1, concat(ATTRIBUTEVALUE2, ATTRIBUTEVALUE3)  )
       as Ovrline  from :lt_att;

  endmethod.

OUTPUT

PRODHRID OVERLINE 
123 COLOR : YELLOW SIZE : 10 GRAMS TYPE: JELLY
456  COLOR : BLUE SIZE : 500 GRAMS TYPE : CREAM 

I have extensively worked on the performance optimization of fiori applications using code push down. I shall talk about the performance optimization techniques for SAP HANA CDS views and SAP HANA Table functions in my next blog post.

Try using these SQL scripts in the AMDP classes instead of using the ABAP layer and do let me know if this made your application run faster.

Leave a Reply

Your email address will not be published. Required fields are marked *