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.
- Declare internal table inside AMDP class
- Declare an ABAP datatype in SQL script
- Delete adjacent duplicates
- Sort by column and pick the latest value
- Convert a delimited string to an internal table
- Apply filter to local table
- Calling AMDP methods with parameters
- Check if the Internal table is not initial
- Select client specific data inside the AMDP method
- 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
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 <> ' ';
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
declare lc_filter string := '( PROD_HR_ID = ' || '''123''' || ' )'; itab_result = apply_filter ( :itab , :lc_filter );
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.
|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.
|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.