一个问题:CDS view在HANA studio里执行,显示的耗时比在ABAP open SQL里少-阿里云开发者社区

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

一个问题:CDS view在HANA studio里执行,显示的耗时比在ABAP open SQL里少

简介: Sent: Samstag, 8. Juli 2017 11:03
+关注继续查看

Subject: RE: have a quick discussion about why the CDS view has a bad performance displayed in ST05 or SAT but the trace displayed in HANA studio shows a good performance


Thanks a lot for your support.


I have executed the report repeatedly for 5 times and the average time in ABAP is still 16 seconds.


image.png


And when the SQLScript is executed in HANA studio, only 2.4 seconds is consumed.

I plan to create an incident to HANA.


Can you please kindly suggest the correct component to create incident? Thanks a lot!


image.png分析方法

Hi Jerry,

there is certainly something which can explain the difference. But sometimes it’s difficult to find the root cause.

One possible reason might be the fact, that from ABAP we use host variables ( where xy = ? instead of where xy = ‘4711’).


If this is identical switch on the expensive statement trace for both cases and try to see, if there is anything visible as a difference.


Jerry的分析 - Sent: Donnerstag, 13. Juli 2017 09:01

Subject: RE: have a quick discussion about why the CDS view has a bad performance displayed in ST05 or SAT but the trace displayed in HANA studio shows a good performance


Hi Heiko,


The HANA colleague in the incident told me the reason of this difference. I copied the reply to this mail for you ( in blue ):


image.pngYou may know that sql optimizer in HANA is rule-based, during execution plan generation for the non-prepared statement, PRELIMIT_BEFORE_JOIN rule can be applied to constant LIMIT operator. But cannot be applied to parameterized LIMIT cases for technical reason, unless we are well aware of how much records could be pruned with LIMIT operator, we cannot estimate its cost and decide the optimal plan based on the estimated cost.


解决方案: using fixed LIMIT value.

p.s:


Prepared statement:


SELECT

/* FDA READ */ DISTINCT "CRMS4V_C_ITEM_OPT2" . "OBJECT_ID" ,

"CRMS4V_C_ITEM_OPT2" . "DESCRIPTION" ,

"CRMS4V_C_ITEM_OPT2" . "POSTING_DATE" ,

"CRMS4V_C_ITEM_OPT2" . "GUID" ,

"CRMS4V_C_ITEM_OPT2" . "PRIORITY" ,

"CRMS4V_C_ITEM_OPT2" . "PRIORITY_TXT" ,

"CRMS4V_C_ITEM_OPT2" . "STATUS_ID" ,

"CRMS4V_C_ITEM_OPT2" . "CONCATSTAT"

FROM /* Entity name: CRMS4V_C_ITEM_OPT2 */ "CRMS4VCITEMODL2" "CRMS4V_C_ITEM_OPT2"

LEFT OUTER MANY TO ONE JOIN /* Entity name: I_BUSINESSPARTNER */ "IBUSINESSPARTNER" "=es_100025" ON "CRMS4V_C_ITEM_OPT2" . "MANDT" = "=es_100025" . "MANDT"

AND "CRMS4V_C_ITEM_OPT2" . "SOLD_TO_PARTY" = "=es_100025" . "BUSINESSPARTNER"

WHERE "CRMS4V_C_ITEM_OPT2" . "MANDT" = ?

AND "CRMS4V_C_ITEM_OPT2" . "PRODUCT_ID" = ?

AND ( RTRIM ( ABAP_UPPER ( "=es_100025" . "LASTNAME" ) ) = ?

AND "=es_100025" . "BUSINESSPARTNERCATEGORY" = ?

OR RTRIM ( ABAP_UPPER ( "=es_100025" . "ORGANIZATIONBPNAME2" ) ) = ?

AND "=es_100025" . "BUSINESSPARTNERCATEGORY" = ? ) LIMIT ?;non-prepared statement:SELECT

/* FDA READ */ DISTINCT "CRMS4V_C_ITEM_OPT2" . "OBJECT_ID" ,

"CRMS4V_C_ITEM_OPT2" . "DESCRIPTION" ,

"CRMS4V_C_ITEM_OPT2" . "POSTING_DATE" ,

"CRMS4V_C_ITEM_OPT2" . "GUID" ,

"CRMS4V_C_ITEM_OPT2" . "PRIORITY" ,

"CRMS4V_C_ITEM_OPT2" . "PRIORITY_TXT" ,

"CRMS4V_C_ITEM_OPT2" . "STATUS_ID" ,

"CRMS4V_C_ITEM_OPT2" . "CONCATSTAT"

FROM /* Entity name: CRMS4V_C_ITEM_OPT2 */ "CRMS4VCITEMODL2" "CRMS4V_C_ITEM_OPT2"

LEFT OUTER MANY TO ONE JOIN /* Entity name: I_BUSINESSPARTNER */ "IBUSINESSPARTNER" "=es_100025" ON "CRMS4V_C_ITEM_OPT2" . "MANDT" = "=es_100025" . "MANDT"

AND "CRMS4V_C_ITEM_OPT2" . "SOLD_TO_PARTY" = "=es_100025" . "BUSINESSPARTNER"

WHERE "CRMS4V_C_ITEM_OPT2" . "MANDT" = '300'

AND "CRMS4V_C_ITEM_OPT2" . "PRODUCT_ID" = 'AB0000000042'

AND ( RTRIM ( ABAP_UPPER ( "=es_100025" . "LASTNAME" ) ) = 'WANG'

AND "=es_100025" . "BUSINESSPARTNERCATEGORY" = '1'

OR RTRIM ( ABAP_UPPER ( "=es_100025" . "ORGANIZATIONBPNAME2" ) ) = 'WANG'

AND "=es_100025" . "BUSINESSPARTNERCATEGORY" = '2' ) LIMIT 100”When I use the fixed limit – I hard coded 100 – in the code, the performance in ABAP becomes equal in HANA studio – 2 seconds to finish the query.



image.pngHowever in CRM WebUI, this limit is specified by end user, it seems we should not hard code it.image.pngAnd in S4 Fiori Search, the limit is not used any more.image.pngWhen end user clicks search in S4, the total number of found product is displayed and only the first 25 product is returned from backend.image.pngimage.pngThis paging logic could be observed in Chrome:

image.pngWhen end user scrolls to the bottom of search list, another 25 products will be fetched. I will discuss this difference with Carsten.

image.png#德国同事的建议 - 使用hintimage.pngMaybe we can solve this with a hint. On other DBs we always had the possibility to specify something like an optimization target “optimize for first rows”, which means that the optimizer should choose a plan, which is optimal for a small number of records.


image.pngThe limit pushdown for parameter value is triggered only if the above hint is given.


P.S.

HANA optimizer is not rule-based but cost-based optimizer. Just for clarification.


令人感到悲伤的是,这个hint在我的场景里没法工作。


From: Wang, Jerry


Sent: Monday, July 17, 2017 7:38 AM

Hi SungHeun,


Sorry for late reply as I am on vacation currently. Thanks a lot for your kind suggestion.


Unfortunately, I guess this hint will not work in my case, as I am using SELECT DISTINCT on the CDS view, which will prevent limit push down even if the hint is applied.


Here below is the hint I have added according to your kind suggestion:


image.pngAnd in the runtime, it still takes more than 200 seconds to finish the query:

image.png

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

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9964 0
说说关于android studio中修改compileSdkVersion的问题(个人总结)
android 6.0出来之后,目前大部分APP应用都打算开始兼容6.0了,兼容6.0主要就是权限的问题,虽然麻烦点但网上还是有很多框架是拿来直接用的,这里不说6.
1028 0
Android Studio列表用法之一:ListView图文列表显示(实例)
前言:         ListView这个列表控件在Android中是最常用的控件之一,几乎在所有的应用程序中都会使用到它。         目前正在做的一个记账本APP中就用到了它,主要是用它来呈现收支明细,是一个图文列表的呈现方式,下面就讲讲具体是如何实现的。
4633 0
ExpandableListView getChildView 不执行,不显示子列表
原因很简单:   在 GroupView 里面不要加入 button 等可点击空间,否则 和 点击 Groupview 展开相冲突。 去掉就好了getGroupView
1485 0
Android自定义View研究--View中的原点坐标和XML中布局自定义View时View触摸原点问题
这里只做个汇总~。~独一无二 文章出处:http://blog.csdn.net/djy1992/article/details/9715047 Android自定义View研究--View中的原点坐标相关问题 我们自定义了View,但是有没想过一个问题,就是View中的(0,0)坐标,也就是原点坐标在哪??我们是不是有时候很困惑,接下来我们就来研究View中的原点坐标相关的问题。
841 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13722 0
Sql server 数据库中,纯SQL语句查询、执行 单引号问题。
原文:Sql server 数据库中,纯SQL语句查询、执行 单引号问题。 在默认值情况下, select 'abc',Titile from tb_Name;  ---输出内容 是abc; 如果想输出 单引号 'abc,需要使用select '''abc',Titile from tb_Name; ---这里用三个单引号'''abc; select '''abc''',Title from tbName;   输出内容是'abc';两边带有单引号; 谨记:如果字符串包含单引号,则需要在单引号前再增加一个单引号。
764 0
2315
文章
0
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载