Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 

Discover CDS views with potential Quick Wins


Gateway performance analysis via /IWFND/STATS gives powerful tools to analyze load on backend S4 system from a volume perspective, among others. What about expensive CDS statements within S4? From SQL Editor in S4 via DBACOCKPIT -> Diagnostics -> SQL Editor we can determine which CDS views can be optimized by running following statement:

  • SELECT TOP 50 plan_size_count AS plansize, *
    FROM m_sql_plan_cache
    WHERE statement_string like '%Z<CDS View(s)>%'
    ORDER BY plansize DESC


Add dbHint annotation and confirm performance benefit









Hints placed on CDS view itself @AbapCatalog.dbHints: [{dbSystem: #HDB, hint: '<hint_name>'}]. Since CDS views are not actual tables, and indices less relevant, the join engines should be considered to optimize performance: OLAP, JOIN, HEX, or ESX engines  (see SAP HANA Performance Developer Guide). With CDS view(s) having potential for performance benefit, run following SQL statement, with and without hints, selecting fields causing joins or emulating long run times with searching on non-key fields, for example:









  • SELECT *
    FROM Z<CDS View>
    WITH HINT ( USE_ESX_PLAN )



Also try following hints, among others from relevant documentation from SAP, to understand engines and associated costs (see corresponding engines and hints below).



  • Column



    • USE_OLAP_PLAN 

    • NO_USE_OLAP_PLAN 

    • NO_USE_HEX_PLAN     


    • NO_USE_ESX_PLAN   




  • HEX



    • USE_HEX_PLAN




  • ESX



    • USE_ESX_PLAN





SAP HANA Execution Engine (HEX)


HEX engine is preferable in this case, with all things remaining the same.





Discover current join engine


What does PlanViz show without dbHints annotation on this particular CDS view? Column, which is the same as without the hint declared in SQL Statement. To confirm set SQL trace using ST05, run odata request, display trace, Execution Trace download as .plv file (see Display Execution Plan below), and upload .plv in Eclipse. Alternatively Graphical Explain will show similar PlanViz information.




Explain Plan -> Graphical Explain will launch Browser




Run Explain Query plan




Execution Trace or PlanViz from Eclipse using Open File... -> .plv





Results


Add dbHints annotation with HEX to CDS view and check performance improved:

@AbapCatalog.dbHints: [{dbSystem: #HDB, hint: 'USE_HEX_PLAN'}]




Results



Odata Gateway result without hint is 6.5 seconds




Odata Gateway result with hint is 1.4 seconds




Conclusion



In conclusion the HEX engine capitalizes on SAP HANA, see note 2570371 - FAQ: SAP HANA Execution Engine (HEX). Without dbHints set to USE_HEX_PLAN in the CDS views, consumption via gateway odata requests using SADL framework, are not using the latest, most innovative query processing engine from SAP.


Thanks for reading

1 Comment