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...'

define view ZLO_MAR_CDSVW

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

    with parameters p_mandt: mandt,

                    p_site : WERKS_D,

                    p_size : WRF_SIZE1,

                    p_lang : spras

as select from 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~matnr maktx mtart matkl size1 werks

   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 just reading 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!