Best Practices with Code Push Down in S4 HANA

« Previous Index Next »

Best Practices: New Open SQL/CDS/CTE/AMDP

Code Push Down is a very effective mechanism to improve the performance of ABAP code, however, the usage should be properly analyzed and planned. A developer generally has multiple options to use when hitting the database. The main options can be:

• New Open SQL
• Core Data Services(CDS)
• ABAP Managed Database Procedure(AMDP)
• Common Table Expressions(CTE)

There may not always code reviews in place and write a first-hand optimal code is important for development, and ABAPers, in that case, have more responsibility when they code. The code should be performing as per HANA’s credentials. There is a clear need of checklist of good programming practices which define the way an ABAPer chooses SELECTing mechanism.

I am trying to compare new SELECTion mechanisms and comparison can be “raw” in nature. (Disclaimer)I am also taking help from another blog Spotlight on ABAP for SAP HANA – Again for the decision-making process in the usage of New SQL and CDS. Please feel free to comment so that this post can be made more useful further for open ABAP development community.

New Open SQL Best practices

• Use when you need to make a SELECT SINGLE or SELECT UP TO ONE ROW
• Use when a query to be used on single table
• Good to use when a simple join on two tables/views/CDS(consider small size category) is required
• Use when standard table buffering can be utilized and can potentially increase performance
• Try to use subqueries whenever possible rather than multiple new open SQL SELECTs

CDS Best practices

• Use when more than two tables(big size category) are to be joined †
• Use when a SQL function is available only in CDS(not in open SQL) and can push down logic to the database. †
• Use when Domain-specific metadata to be used
• Use when CDS can be reused
• Use when SELECT is to be published to oData
• Use when SELECT is to be Access Controlled
• CDS overrides default buffering, so it should not be used where it suppresses table buffering.
• CDS separately provides buffering feature where a CDS view can be buffered just like a Transparent table. CDS buffering can be leveraged using top annotation. Utilize it for the right use case! †

CTE Best practices

• Best to use when series of SELECTs to be written in AS ABAP logic
• Use when multiple interdependent SELECT statements which can’t be joined but to be used one after another either conditionally or by using previous output, CTE can be good candidate for Code Push Down
• CTE can potentially work as a LOOP(WITH-ENDWITH) just like SELECT-ENDSELECT, however, should be used with appropriate use-case
• It is a good practice to merge many SELECTs into one CTE if possible(without complicating CTE) because it reduces the number of DB hits
• CTE adds cherry in performance when used effectively with sub-queries

AMDP Best practices

• AMDP should be used when a large business logic can be written fully in SQL script which executes fully on database layer
  • Of course, you should have right resources in team with skills to do HANA SQL Scripting
• AMDP should not be used for normal SELECTs, JOINs or comparatively smaller logic constructs
• AMDP works with Table Functions and can be used for right use cases where AMDP output can be enriched with CDS metadata and to leverage best of CDS and AMDP features
  • Data Definition Table Function can’t be published to oData services till the time blog was written however we expect CDS, Table function and oData (V4) together have a long way to go
• AMDP should always be used with ABDP BADI framework for two reasons:
  • There is possibility to provide Fallback logic in case of future migration
  • In case of exceptions in Production server, BADI can be deactivated and business process can continue to run with limited features

Use Cases for Code Push Down

† Caution: Know and analyze what you are doing. It is recommended to do a comparison POC before you take a usage decision!

« Previous Index Next »