Skip to Content
Technical Articles
Author's profile photo Santhosini K

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 to the technique of  fetching the records using CDS views and make other calculations/processing/filtration in the ABAP layer.

Here I am referring to both the flavors of CDS views – SAP ABAP CDS views and the external views generated from the SAP HANA CDS views.

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 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 Table functions in SAP HANA using ABAP Managed Database Procedures(AMDP) as a savior.

Since the 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 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
2 123 4/5/2021
3 123 4/6/2021
4 456 4/7/2021
5 456 4/8/2021
6 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 CDS views and 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.

Incase you have a better way of doing this , I am all ears.

 

Cheers,

Santhosini K

Assigned tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Vigneswaran Mathivanan
      Vigneswaran Mathivanan

      Excellent Santhosini...Looking forward to few more blogs related to this topic..

      Author's profile photo Rajiv Kanoria
      Rajiv Kanoria

      Nice Blog Sathosini

      Author's profile photo AjeethKumar R
      AjeethKumar R

      Excellent blog!!..

      Author's profile photo Harry Jing
      Harry Jing

      The blog is only used  for HANA?    R/3  is not used now ?

      Author's profile photo Aman Garg
      Aman Garg

      This is very helpful. Thanks for sharing!

      Author's profile photo Venkat dattatreya
      Venkat dattatreya

      Thanks for the blog.

      For last use case I think string_agg(  ) will also work.

      https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/a924ee1e98ab435a874efa32e6f0ae14.html

      Author's profile photo Santhanalakshmi Sankarakrishnan
      Santhanalakshmi Sankarakrishnan

      Very informative blog. Am sure people can look up to the details whenever they have to implement similar ones in their projects. Keep it up and share more such blogs!

      Author's profile photo Amelia Scott
      Amelia Scott

      Useful Information, your blog is sharing unique information...
      Thanks for sharing!!!

      Author's profile photo Rahul Abrol
      Rahul Abrol

      Thanks for sharing

      Author's profile photo Rakesh Chandra Joshi
      Rakesh Chandra Joshi

      Excellent !  Very informative