如何使用SAP HANA Studio的PlanViz分析CDS view性能问题-阿里云开发者社区

开发者社区> 开发者小助手-bz4> 正文

如何使用SAP HANA Studio的PlanViz分析CDS view性能问题

简介: 如何使用SAP HANA Studio的PlanViz分析CDS view性能问题
+关注继续查看

Part1 – how to test odata service generated by CDS view

Part2 – what objects are automatically generated after you activate one CDS view

Part3 – how is view source in Eclipse converted to ABAP view in the backend

Part4 – how does annotation @OData.publish work

Part5 – how to create CDS view which supports navigation in OData service

Part6 – consume table function in CDS view

Part7 – unveil the secret of @ObjectModel.readOnly

Part8 – my summary of different approaches for annotation declaration and generation

Part9 – cube view and query view

Part10 – How does CDS view key user extensibility work in S4/HANA

Part11 – CDS view test double framework

Part12 – CDS view source code count tool

Part13 – CDS view authorization

Part14 – this blog

Table of Content

Note


Test CDS views used in this blog


How to do performance analysis using HANA studio planviz


All weird performance behaviors get clarified


1. different product ID leads to greatly different performance result


2. select vs select distinct


3. Performance Gap between ST05 trace and the planViz opened in HANA Studio


I am a newbie in CDS performance area and frequently I meet with some “weird” performance behavior which makes me really confused. After I consulted with performance experts, it turns out that all those “strange” behavior has their root cause and could be well explained – no strange at all but just works as designed! I will share with my finding and learning with you through this blog.


Note

(1) This blog is written based on the prerequisite that the Netweaver is connected to a HANA DB whose version listed below:

image.png

(2) The test CDS views I used in this blog are NOT part of SAP standard delivery, so you could NOT find them in any SAP system.


Test CDS views used in this blog

The hierarchy of test CDS views used in this blog is listed below.

(1) CRMS4D_SALE_I and CRMS4D_SVPR_I are two item database tables which store the service order line item with type “Sales Item” and “Service Item”. The record numbers of these two tables are also listed in the picture.

image.png

(2) How the upper-most CDS view CRMS4V_C_ITEM_OPT2 is consumed:

According to different search criteria selected by end user, different SQL statement is populated dynamically:


image.png

(3) CRMS4V_I_ITEM_WO_STATUS_TEXT is just an union of two item database tables whose source code could be found from here.

(4) CRMS4V_C_ITEM_OPT_TXT_DELAY: in search result UI, the status description is displayed:


image.png

However in database table, the status value is modeled as checkbox,

image.png

on the other hand in CRM the status text is defined against status internal key with format I, so the CDS view CRMS4V_C_ITEM_OPT_TXT_DELAY is used to convert the boolean value to the expected internal I format.


image.png

(5) CRMS4V_C_ITEM_OPT2: consumed by ABAP code to serve the search request triggered from end UI.


How to do performance analysis using HANA studio planviz

(1) switch on ST05 trace.

(2) perform search against product id. I write a simple report to trigger the search from backend:

image.png

(3) Execute the search and deactivate the trace. Now the CDS view read operation could be found from the trace.

Edit->Display Execution Plan -> For Recorded Statement:


image.png

(4) In HANA studio, open this plv file:

image.png

With plv file opened in HANA studio, all my previous doubt could be clarified.


All weird performance behaviors get clarified

With trace file available, all weird behavior could be well explained now.


(1) different product ID leads to greatly different performance result


For product ID 3D0000000002, only 0.1 second is used to finish the query, while for product ID AB0000000042, 231 seconds is consumed.


image.png

First open plv file for product ID 3D0000000002, the trace shows there are only 4123 records which fulfills the condition ORDERED_PROD = ‘3D0000000002’:

image.png

This is consistent with what I have found in SE16:

image.png

And for CRMS4D_SVPR_I, there are 20000 records whose product_id = ‘3D0000000002’. So after union, totally 4123 + 20000 = 24123 records are sent for upper process.


image.png

finally these 24123 records are used to get 100 distinct records as result.


Now let’s turn to the trace file for product ID AB0000000042.


A huge number of records (182,272,424 ) are fetched from database table:

image.png

So this search criteria does not make too much sense from business point of view – more search parameter is necessary to limit the records retrieved from database table for further process.


(2) select vs select distinct


Execute below two statements in HANA studio seperately:

case 1: SELECT distinct “OBJECT_ID” FROM “SAPQGS”.”CRMS4VCITEMODL2″ WHERE “PRODUCT_ID” = ‘AB0000000042’ limit 100

case 2: SELECT “OBJECT_ID” FROM “SAPQGS”.”CRMS4VCITEMODL2″ WHERE “PRODUCT_ID” = ‘AB0000000042’ limit 100

Mark the SQL statement in HANA studio, “Visualize Plan->Execute”:

image.png

The execution plan shows that the limit operation is done based on the huge number of records which leads to a comparatively poor performance – 1.1 seconds.


image.png

In the case 2 where the distinct keyword is removed, due to the optimization of HANA execution engine, the limit operation is performed immediately during the time when the records are retrieved from database table, so only these 100 records are sent to subsequent process, which ends up with a comparatively good performance. This behavior is so called “limit push down”, which is not supported if distinct keyword is involved.


image.png

(3) Performance Gap between ST05 trace and the planViz opened in HANA Studio

I have once observed a “strange” behavior:

When I perform the query on my CDS view with two search parameters: product id and sold to party name, it will take on average 16~18 seconds to finish the query.


image.png

When I download the trace file, open and execute it in HANA studio,

image.png

Only around 2 second is consumed in HANA studio.

image.png

image.png

What makes this big difference when CDS view is consumed in ABAP and HANA layer with exactly the same SQL statement?

In fact the statement is not exactly the same at all.


In ABAP layer, the limit is specified dynamically – not fixed in the design time.


image.png

However in HANA studio, the limit 100 is hard coded.

image.png

When I change the dynamically specified limit operation in ABAP with fixed limit, the performance in ABAP is now consistent with HANA studio:

image.png

image.png

The reason is, it is possible for HANA optimizer as a kind of cost-based optimizer to apply PRELIMIT_BEFORE_JOIN rule to constant LIMIT operator during execution plan generation for the non-prepared statement. However due to technical reasons, it is not possible for parameterized LIMIT operator, since it is impossible for HANA optimizer to estimate the cost of the statement containing parameterized LIMIT operator and decide the optimal plan based on the estimated cost – the optimization could not be applied unless we are well aware of how many records could be pruned with LIMIT operator.


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用蓝牙发送文件时需要注意的问题
牙服务注册在蓝牙的Service Database里面,发送文件建立的是RFCOMM的连接,这就需要指定一个channel,类似于TCP/IP的端口,但是具体连接哪一个channel,就需要通过服务查询来获得。
779 0
【巡检问题分析与最佳实践】MongoDB 空间使用问题
阿里云数据库MongoDB的空间使用率是一个非常重要的监控指标,如果实例的存储空间完全打满,将会直接导致实例不可用。一般来说,当一个MongoDB实例的存储空间使用比例达到80-85%以上时,就应及时进行处理,要么降低数据库实际占用空间的大小,要么对存储空间进行扩容,以避免空间打满的风险。 然而,阿里云数据库MongoDB的空间使用情况分析并不简单,本文将由浅入深帮您查看,分析和优化云数据库MongoDB的空间使用。
238 0
UITableView性能优化-中级篇
老实说,UITableView性能优化 这个话题,最经常遇到的还是在面试中,常见的回答例如: Cell复用机制 Cell高度预先计算 缓存Cell高度 圆角切割 等等. . .
404 0
如何使用Visual Studio Code调试PHP CLI应用和Web应用
在按照Jerry的公众号文章 什么?在SAP中国研究院里还需要会PHP开发? 进行XDebug在本地的配置之后,如果想使用Visual Studio Code而不是Eclipse来调试PHP应用,步骤也比较简单。
1078 0
使用MPlayer获取文件信息
关键字: mplayer ffmpeg faac faad lame xvid x264 1. 使用 mplayer分析多媒体文件信息 mplayer -identify -frames 5 -endpos 0 -vo null  YOU-FILE MPlayer SVN-r29319-4.
767 0
call,apply,方法的使用
//apply和call的使用方法 /* * apply的使用语法 * 函数名字.apply(对象,[参数1,参数2,...]); * 方法名字.apply(对象,[参数1,参数2,.
749 0
2315
文章
0
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载