How to analyze query performance for ABAP CDS Views

Purpose

As you may know, ABAP CDS View is one of the important development objects in SAP S/4HANA, which supports multiple purposes including transactional or analytical applications. ABAP CDS Views leverage the power of the underlying HANA DB to boost the performance. However, sometimes the performance is not good enough and needs some fine tuning. There are already some very good blogs and documents out there.

Safeguard Performance of ABAP CDS Views

In this blog, I would like to first come back to the common starting point of the performance analysis for the queries based on the ABAP CDS views. It might be useful to summarize the general approach for the CDS view developers before introducing relatively abstract rules and tips.

ABAP CDS View runtime

Although the ABAP CDS View is developed and stored in ABAP repository, the runtime execution will be pushed down to the HANA database. Below is the diagram picturing the technical architecture. Therefore, we need to focus on the analysis for the execution inside the DB. There are different tools to analyze the performance of single HANA SQL statements, for instance, HANA performance trace, python trace etc. Personally, I think the most useful tool is HANA visualized plan.

Reproduce the issues and initial analysis

Different applications can trigger queries or calls to ABAP CDS Views, for instance, ABAP programs, SAP Fiori Application, reporting tools like SAP Analytics Cloud, SAP Analytics application. Eventually always the ABAP stack triggers the execution of ABAP CDS views. Therefore, when performance issue happens for specific cases, the first tool I would use is Transaction Code “ST12” (Trace), which collects both ABAP traces and SQL traces at the same time. One function of ST12 (the ABAP trace), provides an initial analysis about how much time is spent on ABAP stack and how much time is on HANA DB layer.

In some cases, without this initial analysis, people already started to blame the CDS views or HANA DB as they are the new technology and objects introduced. However, the ABAP trace sometimes indicate the bottleneck can reside in ABAP as well. Of course, one prerequisite for this approach is that we need to know the exact steps to reproduce the performance issues from the frontend applications.

In the ABAP trace, we can sort the calls according to the net time spent respectively. If we see some major time is spent on DB, then we can navigate to the SQL traces by clicking the link “DB->” behind the statement. The typical pattern for the CDS view based call is “FETCH statement <CDS View name>”.

Collect the SQL statement

After we click the link, it brings us to the SQL summary for the related DB calls. Here you can already see some important statistics, for instance, the total records returned.  In some analytical cases, huge amount of data is returned. This might require re-considering the design of the application and whether a reasonable filter is missing in the frontend. In other cases, if we see a high number of executions for the same SQL statement, we need to check if a loop contains the calls to the views in ABAP program and whether the number of loops can be reduced to avoid too many round trips to the database.

Double click on the entry of each call. You will see the complete SQL statement with place holders for variables at first. Copy it for later use.

Click on the slowest execution entry and the statement with values will be showed. The slowest execution with the specific values might be the most representative one to reveal the problem. Copy this statement as well and collect the values from the statement.

The concept of “Literals” (specific values) and “Bind variables” (place holder “?” used for variables) mentioned here is explained below. 

In order to reduce parsing overhead, bind variables are used in many environments like SAP ABAP:

Variant	Example SQL command
Literals	SELECT * FROM DBSTATC WHERE OBJOW = ‘SAPR3’ AND DBOBJ = ‘AFPO’
Bind variables	SELECT * FROM DBSTATC WHERE OBJOW = ? AND DBOBJ = ?

So that the statement with the same structure but different values does not need to be parsed and compiled again and just reuses the existing plan if any.

It can make a significant difference in terms of execution plan, performance and resource consumption if a SQL statement is executed with explicit literals or with bind variables. Therefore, it is recommended that you analyze an expensive SQL statement that uses bind variables in the same way just like the ABAP stack does, i.e. also with bind variables. That’s why we first copy the statement with place holders.

Generate the HANA Visualized plan

To generate the plan, we paste the whole statement with placeholders mentioned in the last step into HANA SQL Editor which is connected to the underlying database of the SAP S/4HANA system. Please do not forget to manually maintain the session variables which impact the generation of the correct plan for queries as well. Those variables were used as the session context from ABAP calls.

For instance,

SET SCHEMA SAPABAP1;

SET ‘CDS_CLIENT’      = ‘010’;

SET ‘APPLICATIONUSER’ = ‘SAPSUPPORT’;

SET ‘SAP_SYSTEM_DATE’ = ‘20200111’;

 -

You can also change the values for your test purpose.

Prepare the specific values for the variable in a line delimited by comma and copy them (ctrl C).

Then, right click on the statement and choose to execute visualized plan.

Please do not exclude the hint and range restriction like below from the statement to be traced because they can also make a difference to the execution plan. 

WITH HINT(NO_JOIN_THRU_AGGR,

RESULT_LAG (‘hana_long’)) WITH RANGE_RESTRICTION(‘CURRENT’)

After the statement with place holders is executed, a pop-up windows asks for the values of variables. Then right click on the statement to add parameter values.

Paste the values prepared. Press OK.

Then the values are filled into the blanks in its sequence maintained.

Click the execution button  on the top right corner. Then the visualized plan is generated.

New ways to collect the Planviz

If your system meets the following prerequisite,

◉ 7.52 SP3 (and higher)

◉ 7.53 SP1 (and higher)

◉ 7.54 All SPs

in the ST05 trace (or from ST12 trace) the HANA plan viz can be collected directly.

Although this way is more convenient to get the HANA visualized plan, but I think the original manual approach can still help you test on the HANA Studio directly. For instance, you can change the session variables, parameters values, the SQL statement itself or adding hints for test purpose.

Generating HANA Visualized plan with DBACockpit.

In case we do not have the access of DB connection inside HANA Studio, another alternative is to use DBACockpit to generate the planviz and test there.

Transaction: DBACOCKPIT.

The SQL Editor can be found under “Diagnostics” folder. Copy and paste the SQL statement with variables. Press “Execution Trace” button.

Fill in the values for the variables and execute.

Then the planviz will be executed and a prompt asks to save the file.

Then the saved planviz can be opened from HANA studio.

With this approach, it is possible to modify the SQL statements but not the session variables. The session variables are taken from the ABAP user session which executes the SQL statement in DBACockpit.