Hi All, following on from looking at ABAP Managed Database Procedures I started looking at CDS views on our CRM on HANA system and wanted to see what was happening at the HANA layer and how the views performed.

Note: our CRM is on ABAP 7.4 and HANA Rev 73.

Pic6.PNG

Pic7.PNG

So first off I created the CDS view. My view is based on Business Partner details. In my project I created a new DDL source and added the following:


@AbapCatalog.sqlViewName: 'ZPM_V_PARTNERS'
define view zpm_partners as
select bt.partner,
bt.name_org1,
bt.name_last,
bt.name_first,
bt.type,
partcat.ddtext,
bt_fs.addrnumber,
adrc.name1,
adrc.city1,
adrc.city2,
adrc.home_city,
adrc.street,
adrc.post_code1,
adrc.house_num1,
adrc.house_num2,
adrc.building,
adrc.floor,
adrc.roomnumber,
adrc.country,
adrc.region
from but000 as bt
    left outer join dd07t as partcat on bt.type = partcat.domvalue_l and partcat.domname = 'BU_TYPE' and partcat.as4local ='A' and partcat.ddlanguage = 'E'
    left outer join but021_fs as  bt_fs on bt.partner = bt_fs.partner
    left outer join adrc as adrc on bt_fs.client = adrc.client and bt_fs.addrnumber = adrc.addrnumber and adrc.langu = 'E'








I saved and activated the view successfully. I then went to the HANA layer and took a look at what was created there. In our CRM schema I found the new view ZPM_V_PARTNERS:

Pic1.PNG

On opening the definition I could see the following create view statement:


CREATE VIEW "SAPSR3"."ZPM_V_PARTNERS" ( "MANDT",
  "PARTNER",
  "NAME_ORG1",
  "NAME_LAST",
  "NAME_FIRST",
  "TYPE",
  "DDTEXT",
  "ADDRNUMBER",
  "NAME1",
  "CITY1",
  "CITY2",
  "HOME_CITY",
  "STREET",
  "POST_CODE1",
  "HOUSE_NUM1",
  "HOUSE_NUM2",
  "BUILDING",
  "FLOOR",
  "ROOMNUMBER",
  "COUNTRY",
  "REGION" ) AS SELECT
  "BT"."CLIENT" AS "MANDT",
  "BT"."PARTNER",
  "BT"."NAME_ORG1",
  "BT"."NAME_LAST",
  "BT"."NAME_FIRST",
  "BT"."TYPE",
  "PARTCAT"."DDTEXT",
  "BT_FS"."ADDRNUMBER",
  "ADRC"."NAME1",
  "ADRC"."CITY1",
  "ADRC"."CITY2",
  "ADRC"."HOME_CITY",
  "ADRC"."STREET",
  "ADRC"."POST_CODE1",
  "ADRC"."HOUSE_NUM1",
  "ADRC"."HOUSE_NUM2",
  "ADRC"."BUILDING",
  "ADRC"."FLOOR",
  "ADRC"."ROOMNUMBER",
  "ADRC"."COUNTRY",
  "ADRC"."REGION"
FROM ( ( "BUT000" "BT"
  LEFT OUTER JOIN "DD07T" "PARTCAT" ON ( "BT"."TYPE" = "PARTCAT"."DOMVALUE_L"
  AND "PARTCAT"."DOMNAME" = 'BU_TYPE'
  AND "PARTCAT"."AS4LOCAL" = 'A'
  AND "PARTCAT"."DDLANGUAGE" = 'E' ) )
  LEFT OUTER JOIN "BUT021_FS" "BT_FS" ON ( "BT"."CLIENT" = "BT_FS"."CLIENT"
  AND "BT"."PARTNER" = "BT_FS"."PARTNER" ) )
LEFT OUTER JOIN "ADRC" "ADRC" ON ( "BT_FS"."CLIENT" = "ADRC"."CLIENT"
  AND "BT_FS"."ADDRNUMBER" = "ADRC"."ADDRNUMBER"
  AND "ADRC"."LANGU" = 'E'
  AND "BT"."CLIENT" = "ADRC"."CLIENT" ) WITH READ ONLY







Nothing too out of the ordinary here except I did notice one subtle thing – the inclusion of opening and closing brackets () around the datasource joins.

( ( “BUT000” “BT”

  LEFT OUTER JOIN “DD07T” “PARTCAT” ON ( “BT”.”TYPE” = “PARTCAT”.”DOMVALUE_L”

  AND “PARTCAT”.”DOMNAME” = ‘BU_TYPE’

  AND “PARTCAT”.”AS4LOCAL” = ‘A’

  AND “PARTCAT”.”DDLANGUAGE” = ‘E’ ) )

  LEFT OUTER JOIN “BUT021_FS” “BT_FS” ON ( “BT”.”CLIENT” = “BT_FS”.”CLIENT”

  AND “BT”.”PARTNER” = “BT_FS”.”PARTNER” ) )

LEFT OUTER JOIN “ADRC” “ADRC” ON ( “BT_FS”.”CLIENT” = “ADRC”.”CLIENT”

  AND “BT_FS”.”ADDRNUMBER” = “ADRC”.”ADDRNUMBER”

  AND “ADRC”.”LANGU” = ‘E’

  AND “BT”.”CLIENT” = “ADRC”.”CLIENT” )

Would this have an affect on the execution and performance of the query? To investigate this I decided to compare the execution of the view with the execution of the raw SQL query.

In my HANA Studio SQL Editor I queried the view for one business partner a number of times to get the average execution time:


select * from "SAPSR3"."ZPM_V_PARTNERS" where partner = '1000184087'





And then executed the raw query without any of the opening or closing brackets querying the same partner:


select bt.partner,
bt.name_org1,
bt.name_last,
bt.name_first,
bt.type,
partcat.ddtext,
bt_fs.addrnumber,
adrc.name1,
adrc.city1,
adrc.city2,
adrc.home_city,
adrc.street,
adrc.post_code1,
adrc.house_num1,
adrc.house_num2,
adrc.building,
adrc.floor,
adrc.roomnumber,
adrc.country,
adrc.region
from sapsr3.but000 as bt
    left outer join sapsr3.dd07t as partcat on bt.type = partcat.domvalue_l and partcat.domname = 'BU_TYPE' and partcat.as4local ='A' and partcat.ddlanguage = 'E'
    left outer join sapsr3.but021_fs as  bt_fs on bt.partner = bt_fs.partner
    left outer join sapsr3.adrc as adrc on bt_fs.client = adrc.client and bt_fs.addrnumber = adrc.addrnumber and adrc.langu = 'E'
where bt.partner = '1000184087';





The SQL plan cache was interesting. The raw SQL query without the opening and closing brackets was 3 times faster than the query on the view.

Pic2.PNG

It would seem that the brackets are causing the view to perform slower. Just to confirm I took the SQL from the generated view and executed that a number of times querying on the same partner again:


SELECT
  "BT"."CLIENT" AS "MANDT",
  "BT"."PARTNER",
  "BT"."NAME_ORG1",
  "BT"."NAME_LAST",
  "BT"."NAME_FIRST",
  "BT"."TYPE",
  "PARTCAT"."DDTEXT",
  "BT_FS"."ADDRNUMBER",
  "ADRC"."NAME1",
  "ADRC"."CITY1",
  "ADRC"."CITY2",
  "ADRC"."HOME_CITY",
  "ADRC"."STREET",
  "ADRC"."POST_CODE1",
  "ADRC"."HOUSE_NUM1",
  "ADRC"."HOUSE_NUM2",
  "ADRC"."BUILDING",
  "ADRC"."FLOOR",
  "ADRC"."ROOMNUMBER",
  "ADRC"."COUNTRY",
  "ADRC"."REGION"
FROM ( ( sapsr3."BUT000" "BT"
  LEFT OUTER JOIN sapsr3."DD07T" "PARTCAT" ON ( "BT"."TYPE" = "PARTCAT"."DOMVALUE_L"
  AND "PARTCAT"."DOMNAME" = 'BU_TYPE'
  AND "PARTCAT"."AS4LOCAL" = 'A'
  AND "PARTCAT"."DDLANGUAGE" = 'E' ) )
  LEFT OUTER JOIN sapsr3."BUT021_FS" "BT_FS" ON ( "BT"."CLIENT" = "BT_FS"."CLIENT"
  AND "BT"."PARTNER" = "BT_FS"."PARTNER" ) )
LEFT OUTER JOIN sapsr3."ADRC" "ADRC" ON ( "BT_FS"."CLIENT" = "ADRC"."CLIENT"
  AND "BT_FS"."ADDRNUMBER" = "ADRC"."ADDRNUMBER"
  AND "ADRC"."LANGU" = 'E'
  AND "BT"."CLIENT" = "ADRC"."CLIENT" )
where bt.partner = '1000184087';





As expected the SQL plan cache showed similar average run-time to the view.

Pic8.PNG

To take it a bit further I took a look at the viz plans of the view and the raw SQL (without the brackets).

Straight away it was easy to see the differences in the plan. For reference purposes I have attached the viz plans to the blog.

Here is the overall view of the viz plan for the CDS View:

Pic3.PNG

On drilling down:

Pic10.PNG

Pic11.PNG

On analysis, first off it joins BUT000 to BUT021_FS, assembles the results, then joins ADRC to DD07T, assembles the results and then joins up these 2 result sets all in a linear execution it seems.

And here then is the viz plan for the direct query

Pic4.PNG

Pic9.PNG

As you can see from the plans for the direct query the joins on BUT000 and DD07T and the joins on ADRC and BUT021_FS look to be done in a parallel execution and then the results are assembled.

Conclusion

So the inclusion of the opening and closing brackets surrounding the datasource joins are resulting in a different execution plan for the view compared to the execution plan of the directly executed query. From the evidence above this has a negative impact on the execution time of the view. Would be interesting to hear from some SAP folks if this is by design and if so are there best practice gudelines available in terms of performance optimization on CDS views….

To report this post you need to login first.

6 Comments

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

  1. Sean Holland

    hi Peter, Interesting stuff, as discussed this looks like a bug and hopefully we can get a response from some SAP folks, I don’t think they would specifically want the CDS views to behave any differently due to the format of the script that’s deployed.

    (0) 
  2. Jens Weiler

    Hi Peter,

    First of all many thanks for this blog and the detailed description and analysis. You can be sure that we will investigate and discuss your example internally with the experts and see why those brackets have been set (respectively analyze the performance of the create statements). As soon as I got the experts together, I will come back to you.

    Cheers

    Jens

    (0) 
      1. Lars Breddemann

        Hmm… looking at a similar example with rev. 94 (that’s 21 revisions after this blog!) I am not able to reproduce the different execution plan behavior by introducing brackets.

        This is likely because the query optimization and transformation had been largely improved and extended meanwhile.

        So, I’d say this issue is not present in current versions anymore.

        – Lars

        (0) 

Leave a Reply