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.
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:
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.
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.
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:
On drilling down:
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
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....
- SAP Managed Tags:
6 Comments
