Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
santhosini_K
Product and Topic Expert
Product and Topic Expert
33,211
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
10 Comments
Vigneswaran_Mathivanan
Product and Topic Expert
Product and Topic Expert
0 Kudos
Excellent Santhosini...Looking forward to few more blogs related to this topic..
rajiv_kanoria
Product and Topic Expert
Product and Topic Expert
0 Kudos
Nice Blog Sathosini
hatrigt
Participant
0 Kudos
Excellent blog!!..
1190_5939_439
Active Participant
0 Kudos
The blog is only used  for HANA?    R/3  is not used now ?
former_member14709
Contributor
0 Kudos
This is very helpful. Thanks for sharing!
dv_u
Participant
0 Kudos
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/a924ee1e98ab435a874efa32e6...
0 Kudos
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!
former_member732024
Discoverer
Useful Information, your blog is sharing unique information...
Thanks for sharing!!!
rahulabrol
Explorer
0 Kudos
Thanks for sharing
joshidjosh
Advisor
Advisor
0 Kudos
Excellent !  Very informative