Skip to Content
Personal Insights

Optimize ABAP CDS Performance – Double-Query Approach

Hello all !

Quite some time ago, I found a nice way to overcome some performance and logical issues when using ABAP CDS views.

Scenario Description

I was using standard SADL framework to request the data for my OData services from ABAP CDS views, but I was constantly running into either performance or functional issues.

Regarding performance, I guess, it’s clear – all was slow, even GET_ENTITY.

Regarding functional ones, it’s a bit more complicated. But I will try to explain. OData v2 (especially in SAP’s realization) does not really support complex queries through dependent objects.

For instance you have a document header and then a document item with product, a partner, dates etc, and all with 1:N cardinality to the header. Now imagine you need to search trough those products + partners + dates + some header fields simultaneously. If you never programmed any OData, just believe me, it won’t be so easy, if possible at all…

Idea

And so, I came to the idea to split the queries into two:

  • First, we read distinct CDS Entity Key, based on the provided search criteria;
  • Second, we read the required CDS data, based on the selected keys.

As you may notice, this will allow us to use two different CDS views. One is Search View and another one is Data View. Data View represents CDS Entity and by definition has one line per Entity. Search View is needed to get keys, so that it may have whatever you want, any fields, from any entities (product/partner/dates) and must not be 1:1 to the entity.

Note: this approach may work, but also may not. Or even sometimes it will be working (fast), sometimes not. It depends…

 

My Example

Let’s come to one of my examples….

I have a CDS view, containing 33 very big tables.

My CDS View Definition

After adding date information (marked red box), the performance got just terrible.

So, I’ve changed the key of the entity from Object ID (NUM10) to Object GUID (RAW16), which is then used everywhere as JOIN condition, but also a part of primary database indexes.

Then I had to split the query into two as described above.

 

How Was It Originally

So, imagine you have your SADL implementation and request one entity (GET_ENTITY).

The following query will be executed:

Simulation of SADL Query

HANA Studio shows about 15 seconds of runtime. Not bad just for one line…

Plan Viz:

 

Result: One query is executed in 12 seconds, 45+ million rows read, 28 GB RAM allocated. Not bad!

P.S. Obviously our keys are not passed to the views, and they are read fully.

 

New Implementation

Ok, now let’s try to split the queries…

First Query (Getting CDS Key):

Result: Query is executed in 10 milliseconds, 1 row was read, 1,2 MB RAM allocated.

 

Second Query (Getting Data by CDS Key):

 

Result: Query is executed in ~100 milliseconds, single rows selected, 43 MB RAM allocated.

 

Just to sum it up…

Way to get data Response time # Rows RAM Consumption
Single query ~ 12 seconds ~ 50.000.000 ~ 29 GB
Double query ~ 100 milliseconds ~ 30 ~ 45 MB

 

My OData Framework

Writing complicated GET_ENTITY or GET_ENTITYSET methods might become boring, so that I’ve developed a framework, which implement my idea. With that the implementation of the GET_ENTITYSET methods looks like below:

Example of GET_ENTITYSET method

Looks quite elegant. Feel the same, give it a LIKE.

All the best!

Dima

1 Comment
You must be Logged on to comment or reply to a post.