CDS: CDS Performance Analysis

 

CDS Performance Analysis:


CDS Views operate on the code pushdown framework where the data intensive calculations/aggregations are done in the DB layer (as opposed to the traditional application layer), which provides a boost to performance.

Everyones expectation is that with the code pushdown the magic should happen every time. The performance should always be better if not optimized. But friends in real project scenarios, everything is not a happy path. There have been instances where the query does not provide the expected performance uplift. In this multi-part series, we will first look at analyzing the performance of the underlying query and then articulate different ways to tune it.

The internet is flooded with Core Data Services, but you will hardly find any good tutorials or series on Optimization and Performance Tuning of ABAP Core Data Services. Hopefully, this series will help our SAP Fraternity. 🙂

Basics First.

  • ABAP CDS views are developed using ADT and their execution happens in the DB.
  • Analytical Query CDS behave differently from Consumption View CDS because of the underlying annotations which impact performance (We will discuss this in Part 2).
  • It is possible to troubleshoot Analytical Query CDS through transaction RSRT to identify Fiori or authorization related issues. This is one of the first steps recommended to isolate and identify if the issue is performance related.
  • To understand the execution, we need to identify the SQL statement that is generated by the CDS. (Here we must pay close attention to the actual variables and value that are used).

For example: Execution of Trial Balance CDS (IFIGLBALCUBE/ I_GLACCTBALANCECUBE) in 1709 produces the following SQL statement.

Do not worry, we will see in the subsequent posts on how to obtain this SQL statement.

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')
  • If the CDS view has authorization restrictions via DCL (Data Control Language) this will play a crucial part in the SQL statement formed. In the previous statement, “CDS access control” indicates that a corresponding DCL exists and authorization restrictions have been applied during execution.
  • If CDS query has DB hints (prev example has HINT(DOUBLE_PREAGGR_BEFORE_JOIN,USE_OLAP_PLAN,RESULT_LAG (‘hana_long’))) as part of the annotations, this will also play a part in the execution plan(creation of the SQL statement) of the CDS.
  • CDS views operate on a stacking model. (Views are stacked and reused to achieve greater scalability and robustness). If the stacking is sub optimal, this will be a drain on the performance.

How to understand the stacking model?

In ADT, after opening the related CDS view, right click and choose

Open With -> Dependency Analyzer.



  • This will open the SQL Dependency Tree which shows how the selection happens from the top down


Pay Special attention to the Tabs at the bottom of the screen.

  • SQL Dependency Graph is a visual representation of the Tree and helps you better understand the database selection. (This is a good tool to use to remove any unwanted branches from the SQL query to optimize the performance).
  • Complexity Metrics give a clear indication of the number of database tables used by the CDS view. (please note that runtime associations are not included in this list only design time associations, joins, unions, aggregations etc.)

This gives a basic idea on how CDS view operate under the hood and what are the important considerations that affect performance. In the following posts, we will look at the SQL statement, Plan Viz, Execution Plan and Annotations to fine tune and achieve optimal performance.



Comments

Post a Comment

Popular posts from this blog

CDS: Expose CDS Views as OData Service through Annotation

Image
  Expose CDS Views as OData Service through Annotation: If you are following our series on  SAP ABAP on HANA  then you would be familiar with  CDS Views . If not, then please check our  HANA ABAP Part IV  where we introduced  Core Data Services  and Part V where we took a  Deep Dive into CDS Views . Also, if you have been taking the advantage of our  SAP Netweaver Gateway and OData Services Tutorial  series, you would know that  SEGW  is the t-code to create OData Projects and eventually publish an OData Service. But would you not be surprised if we say, you can create your OData Projects without going to SEGW transaction?  Today, we would show you how you can  expose CDS View as OData Services with just some Annotations  (i.e SQL code). Introduction We have substantially explored the CDS views with major functionalities.  CDS provides another magical strength to the users to expose the views as OData s...
Read more

SAP ODATA: Debugging, Trace, Cache Cleanup and F4 Help

Image
  Debugging, Trace, Cache Cleanup and F4 Help: By this time we are optimistic that all the readers are conversant with the creation of Projects in  SEGW , mapping of the  Data Models ,  Navigation ,  Service creation  and  Testing URIs . Assuming that you have gone through the previous six tutorials on  SAP Netweaver Gateway and OData Services . If you have not,  please spare some time aside from your busy schedule and invest it on educating yourself  in coming days. We believe, every ABAPer would need to have a good grasp of OData and SAPNetweaver Gateway development concepts. Once we start working on real projects, our developments would not be as simple as shown in the tutorial exercises.  We get weird errors/issues ranging from Authorization to Configuration to Code issues . As an ABAPer, we need to learn how to find the root cause of the issues and mitigate them. Today, let us venture further and understand how we will be handl...
Read more

SAP ODATA: Media Handling using OData Gateways

Image
  Media Handling using OData Gateways: Media Handling using OData-Gateways Introduction The conundrum  (confusion/difficulty)  of handling media through gateway service is gone. If you have a requirement of showing PDF or sending images/music over the gateway, you can do it seamlessly. While in recent times we get a torrent of requirements of Smartforms printing, very fewer opportunities are there to pass image/music files. This article should give a fair idea about the tit-bits of handling PDF printing in the gateway. Steps Step-1: Create Entity to Handle Media Information Create a gateway project (Follow the previous blogs for details of creating a project and subsequent actions). Create a separate entity to handle the media operations. Don’t forget to tick the ‘Media’ check box as this indicates the entity as media. In figure ‘SoPrint’ entity is marked as Media. Step-2: Generate the runtime artifacts Generate the runtime artifacts of the project. All the required metho...
Read more