A kill to an ABAP CDS View?

In the first blog of this series we considered that CDS views are not just for the super cool HANA types, but can also be used to escape when marooned on planets with nothing but a lowly Oracle DB. Many of SAP’s customers I suspect have only just begun to dip their toes into these scary HANA waters, and so its important to understand what of this new technology we can learn and use today (whilst still on the traditional non-HANA DB); and what must wait for a possible future HANA implementation.

In today’s blog we will consider the performance of the ABAP CDS view with an Oracle DB, if we are without the HANA DB in the back end; all from the perspective of a certain double-o secret agent. In Teched I posed the question to an SAP expert, (Who shall remain nameless, less Spectre (of course we all know that SAP==Spectre ABAP Programmers) learn of his details and track him down), here I was lead to believe that even on Oracle the use of the ABAP CDS views would give major performance benefits over the equivalent SQL statement. I have to say I had my suspicions that this may have been a double agent trying to lure me, and so without further ado on returning from TECHED set out to test the ABAP CDS view tech with the help of my very own 00-agent and the necessary 00 CDS-View.

So first to construct my CDS-view I made a join, both literally (between 3 Oracle DB tables) and metaphorically between 2 infamous Spectre CDs that I happen to have in my secret agent archives, creating a very real CDs view.

The literal CDS-View is made by creating the DDL source, here I reused the same DDL source as from my the previous blog.

<DDL source for CDS view>

@AbapCatalog.sqlViewName:‘ZLOMARCDS001’

@ClientDependent:true

@AbapCatalog.compiler.CompareFilter:true

@AccessControl.authorizationCheck:#CHECK

@EndUserText.label:‘Material master CDS View for secret agents…’

defineview ZLO_MAR_CDSVW

(matnr, maktx, mtart, matkl, size1, werks )

withparametersp_mandt:mandt,

p_site :WERKS_D,

p_size :WRF_SIZE1,

p_lang :spras

asselectfrom mara

join marc on mara.matnr = marc.matnr

join makt on mara.matnr = makt.matnr {

key mara.matnr,

makt.maktx,

mara.mtart,

mara.matkl,

mara.size1,

marc.werks

}

where makt.mandt =:p_mandt

and makt.spras =:p_lang

and mara.mandt =:p_mandt

and mara.size1 =:p_size

and marc.mandt =:p_mandt

and marc.werks =:p_site

;

I also have spent some time deciphering the DDL source, (with the help of Q-division) to turn it into the equivalent standard Open SQL statement.

<SQL for the equivalent >

SELECT mara~matnrmaktxmtartmatklsize1werks

FROM mara

INNER JOIN marc ON mara~matnr = marc~matnr

INNER JOIN makt ON mara~matnr = makt~matnr

INTO TABLE et_spacesuits

WHERE mara~size1 = iv_size

AND marc~werks = iv_site

AND makt~spras = iv_lang.

Now what we are interested in is to establish, will the CDS-view help our secret agent perform better than the more traditional viewless open SQL statement???

To put this to the test we need to perform some traces, and establish if we have the ultimate performing secret agent or not. So we navigate our way to ST12; the top-secret performance analysis tool for single transaction analysis. In ST12 we will perform some traces in the current mode for both the CDS-View and the equivalent SQL code, and then we will review the performance of the Oracle database with each solution. Now I know before Spectre send their agents around to do me in, that DB performance tracing is a bit of a minefield, the first time you run a SQL statement, the DB will likely cache the results and then the second time you run you are really justreading from the cache, and not from the DB. However our system caches are cleared once per day, and so the first run of the day with each solution should give a rough indication of the typical performance of each solution. This testing has now been performed several times within our SAP ECC Oracle DB solution.

Then to test our metaphorical solution I kitted out my own secret agent with both the CDS-View technology (these I believe are also known as SPECTRECLES), and the old fashioned non-spectrecles solution. Our agents job was to asses which tech made him feel most like a secret agent, and enable him to perform and to shoot straight. We will see that both the metaphorical testing and the literal testing reach the same conclusion.

So first to the metaphorical testing, and we kit out our double-O secret agent with his very own CDs views:

We then perform the literal system test using this technology in our backend system:

Then we resort back to our traditional non-CDS view (traditional SQL), and test again:

The metaphorical performance is definitely increased and looks way less dorky!

This is also reflected in the DB results – 17K ms as opposed to 27Kms with the CDS-views.

Well as you can see the SQL statement result (and trust me that I performed this test many times) on average performs a bit faster than the CDS views. Our secret agent agrees, that while the SPECTRECLES look cool, they aren’t much use for quick fire shooting (dad they are rubbish!) I think was the comment from our agent in the field.

Then a member of Spectre ABAP programmers (one that likes to write really THICK books) left an intriguing comment on my last blog, which confirmed my suspicions, the ABAP CDS-views are actually implemented in native SQL, and so at their very best will perform similar to the corresponding open SQL statement. Its just a question of what can be turned to native SQL faster the Open SQL or the CDS-view. This can be seen in the results, I suspect there is a little overhead prior to the DB layer to turn the CDS view into its equivalent SQL, and hence the slight drop in performance that we see for the CDS view. This should mean that for more heavy duty selections, the ABAP CDS-view performance is more equivalent to the Open SQL.

Unfortunately another disadvantage I discovered with running the CDS-Views on Oracle in our systems is that there is no shared cursor cache information recorded for the CDS views that is viewable in the DBACOCKPIT transaction , so we are unable to really see the nitty gritty of how these statements are implemented. It would be nice to know if the failure to update the cursor cache is a particular issue with the CDS views, or maybe its some system setting we are missing.

So is this a kill to a CDS-View?

Well not really, it just means that this technology is close to equivalent in performance to the corresponding open SQL, and will likely become more equivalent as the work involved in the SQL statement overtakes that of translating the CDS view to the native SQL. Should we then not bother with the CDS-View prior to implementing HANA? or are there real reasons why they may still make sense? This is the question we will try to address in my final blog of the series The ABAP CDS-View Strikes Back!

6 Comments

Nice blog, just to clarify: When you activate a CDS view, you instantiate a database view or SQL view on the database. The name of that view is defined in the DDL source and yours is ZLOMARCDS001. You find it in SE11 and there via Utilities -> Database object also the SQL used on your DB. And it is the performance of the access to this SQL view, you are measuring. If you would create the same view classically in SE11 you would find exactly the same!

The DDL of CDS enhances the view building capabilities compared to SE11 fundamentally. But drilling it down to DB level it is CREATE VIEW in both cases. No performance wonders to be expected compared to a JOIN in Open SQL. But much more modelling capabilities. And still the old rule holds: If you don’t want to reuse it or its not part of a framework (e.g. connected to ODATA), why not use an Open SQL join.

Thanks Horst, I much appreciate the clarification. I’m still a bit puzzled why the selection on the views are not showing any results in the shared cursor cache, all I get is zeros across the board, is that normal for views? I’ll try to cover a little of what they bring to the table in my next blog – although its a challenge for me as this is all new stuff to me, but fun none the less.

I think there are a few good reasons for using the views ahead of the joins, and that is why the views will strike back!

why the selection on the views are not showing any results in the shared cursor cache

A database view (and this is the same for CDS views as well as classical SE11 views) is defined with CREATE VIEW on the database. At runtime the database resolves the SELECT statement used to access the view and the SELECT statement used in the view itself in order to access the database tables adressed by the view. How that is done is data base specific. There are some threads in the Web that discuss “shared cursor cache” in Oracle. I’d say that cursors are set for the tables itself and not for the views.

OK thanks, I will dig around a bit, but I get that these are just SE11 views, but with additional functionality (none of which actually brings performance benefits over standard SE11 views), thanks for being very clear on that.

I also wondered what the performance of CDS Views would be like an a non HANA DB.

It would be interesting comparing the generated native SQL created from a CDS View with that of an Open SQL equivalent query. I am guessing it would be a lot more verbose to cater for all the functions and features.

Thinking out loud, but maybe there is a trade off between additional features and vendor specific optimizations like optimizers and hints etc.

JSP

ps. I found a copy of the Interface Adviser recently, was my bible in the 90’s