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