# 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.

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:

JerryWang，2007年从电子科技大学计算机专业硕士毕业后加入SAP成都研究院工作至今。Jerry是SAP社区导师，SAP中国技术大使。2020年5月下旬，Jerry做了脑部肿瘤的开颅切除手术，对编程和人生又有了新的感悟。

11-18 9920
08-15 1万+
08-14 5421
06-18 1222
06-30 1万+
05-24 6748
11-19 1万+
03-27 6512
08-04 8458
05-01 7456
05-27 6894
03-06 2546
09-11 1万+
12-26 819