CDS – 23: Fundamentals of CDS Performance Analysis

With CDS Views, speed is improved since data-intensive computations and aggregations are performed at the database layer (rather than the usual application layer) using the code pushdown architecture.

Everyone expects the magic to happen every time with the code pushdown. If not optimized, performance ought to be higher at all times. However, in actual project circumstances, nothing goes as planned. There have been cases where the query fails to deliver the anticipated improvement in performance. In this series of several parts, we will first examine how well the underlying query is doing and then discuss several tuning strategies.

Although there are a ton of Core Data Services available online, it is rare to find quality courses or series on ABAP Core Data Services optimization and performance tuning. This series should benefit our SAP Fraternity, perhaps. 🙂

First, the basics.

ADT is used in the development of ABAP CDS views, which are then executed in the database.
Because of the underlying annotations that affect performance, Analytical Query CDS operate differently from Consumption View CDS (We will address this in Part 2).
Analytical Query CDS can be troubleshooted via transaction RSRT in order to find authorization or Fiori related problems. One of the first things that should be done to isolate and determine whether the problem is performance-related is this.

As an illustration, the SQL statement that results from the Trial Balance CDS (IFIGLBALCUBE/I_GLACCTBALANCECUBE) execution in 1709 is as follows.

Fear not—we’ll learn how to get this SQL statement in the posts that follow.

SELECT /* CDS access control applied */ "COMPANYCODE" "K____360" , 
"CHARTOFACCOUNTS" "K____359" , 
"GLACCOUNT" "K____366" , 
"FISCALPERIODDATE" "K____443" , 
"COMPANYCODECURRENCY" "K____479" , 
COUNT(*) "Z____110_SUM" , SUM( "DEBITAMOUNTINCOCODECRCY" ) 
"Z____3159_SUM" , SUM( "ENDINGBALANCEAMTINCOCODECRCY" ) 
"Z____3163_SUM" , SUM( "CREDITAMOUNTINCOCODECRCY" ) 
"Z____3187_SUM" 
FROM /* Entity name: I_GLACCTBALANCECUBE CDS access controlled */ 
"IFIGLBALCUBE" ( "P_FROMPOSTINGDATE" => ? , "P_TOPOSTINGDATE" => ? ) 
"A1" WHERE "MANDT" = ? AND "COMPANYCODE" = ? AND 
"LEDGER" = ? GROUP BY "COMPANYCODE" , "CHARTOFACCOUNTS" , 
"GLACCOUNT" , "FISCALPERIODDATE" , 
"COMPANYCODECURRENCY" ORDER BY "A1" . "COMPANYCODE" , "A1" . 
"GLACCOUNT"  WITH HINT
(DOUBLE_PREAGGR_BEFORE_JOIN,USE_OLAP_PLAN,RESULT_LAG ('hana_long')) 
WITH RANGE_RESTRICTION('CURRENT')

In the formation of the SQL statement, it will be important to know whether the CDS view has authorization limitations via DCL (Data Control Language). The term “CDS access control” in the preceding sentence denotes the application of permission limitations during execution and the existence of a matching DCL.
If the annotations for the CDS query contain database hints (the previous example contains HINT(DOUBLE_PREAGGR_BEFORE_JOIN,USE_OLAP_PLAN,RESULT_LAG (‘hana_long’))), this will also affect the CDS’s execution plan, which creates the SQL statement.
CDS views use a stacking approach of operation. (To increase scalability and resilience, views are stacked and reused). There will be a performance hit if the stacking is not ideal.

How is the stacking model to be understood?

After the relevant CDS view opens in ADT, right-click and select

Select Dependency Analyzer to Open With.

This will display the SQL Dependency Tree, which illustrates the top-down process of selection.

Keep a close eye on the tabs located at the bottom of the screen.

The SQL Dependency Graph aids in your understanding of the database choice by providing a visual depiction of the Tree. (To improve efficiency, this is a useful tool to utilize to eliminate any unnecessary branches from the SQL query).

Complexity Metrics provide a clear picture of how many database tables the CDS view uses. (Note that this list only includes design-time associations, joins, unions, aggregations, etc.; runtime associations are not included.)

This provides a fundamental understanding of how CDS view functions internally and highlights the key factors that influence performance..