一个问题：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.
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!
分析方法
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 ):
“It's the performance gap between prepared statement and non-prepared statement, when LIMIT is invovled.
You 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.
However in CRM WebUI, this limit is specified by end user, it seems we should not hard code it.
And in S4 Fiori Search, the limit is not used any more.
When end user clicks search in S4, the total number of found product is displayed and only the first 25 product is returned from backend.
This paging logic could be observed in Chrome:
When end user scrolls to the bottom of search list, another 25 products will be fetched. I will discuss this difference with Carsten.
#德国同事的建议 - 使用hint
Hi Jerry, ah, I see. As suspected a difference between prepared / non-prepared statements. And the problem with the limits as host variables is not showing up the first time.
Maybe 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.
WITH HINT (PRELIMIT_BEFORE_JOIN)
The 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:
And in the runtime, it still takes more than 200 seconds to finish the query: