Alternative Strategies for HANA Performance

Hello Folks,

It’s been a long time since i blogged.

I have seen lot of performance questions in community forums.

Today i would like to share few tips  which would make a huge impact in terms of performance and memory utilization.

I will try to explain 3 scenarios here

Case 1) Optimal Use of Date Input Parameter in any HANA Views

Case 2) Effect of If Statements in Calculated columns

Case 3) Correct approach of using Input Parameters

Case 1) Optimal Use of Date Input Parameter in any HANA Views

By now all of you know that using Input Parameter is a step in right direction than using a variable ..But now we will see the effect of input parameter in reading data from a table when used in different ways

Step1) Create an Input Parameter – With DATE Data type as shown below

Step2) Apply the filter on the table -Since Most of the SAP tables bring the dates as NVARCHAR unless you map to a date field in SLT .. For this example the data in table is in the NVARCHAR Format

Option1) Convert the column from the table into a date format as shown below and then apply filter on the calculated column

Calculated Column :

Apply Filter on Calculated field

“DT_AUGDT” <=‘$$IP_CLEARINGDATE$$’

Option2) Convert the input parameter field to match the format of the table

In this case i would apply the input parameter directly without creating any calculated column

filter would be something like this

“AUGDT” <= replace(‘$$IP_CLEARINGDATE$$’,‘-‘,”)

Now let’s look at the comparison of the both the views

Option1 )

SELECT TOP 200 DISTINCT SUM(“DMBTR”) AS “DMBTR_SUM”, SUM(“WRBTR”) AS “WRBTR_SUM” FROM “_SYS_BIC”.”system-local.private.Temp.perf/INPUTDATE”(‘PLACEHOLDER’ = (‘$$IP_CLEARINGDATE$$’, ‘2017-01-01’));

Statement ‘SELECT TOP 200 DISTINCT SUM(“DMBTR”) AS “DMBTR_SUM”, SUM(“WRBTR”) AS “WRBTR_SUM” FROM …’

successfully executed in 340 ms 401 µs (server processing time: 148 ms 697 µs)

Option2)

SELECT TOP 200 DISTINCT SUM(“WRBTR”) AS “WRBTR_SUM”, SUM(“DMBTR”) AS “DMBTR_SUM” FROM “_SYS_BIC”.”system-local.private.Temp.perf/INPUTDATE1″(‘PLACEHOLDER’ = (‘$$IP_CLEARINGDATE$$’, ‘2017-01-01’));

Statement ‘SELECT TOP 200 DISTINCT SUM(“WRBTR”) AS “WRBTR_SUM”, SUM(“DMBTR”) AS “DMBTR_SUM” FROM …’

successfully executed in 38 ms 992 µs (server processing time: 23 ms 146 µs)

Results: As you can see we have a runtime improvement of more than 10 times.

Now let’s analyze the memory utilization of both the options – I pulled this from Viz Plan

Option1)

Option2 )

Results: As you can see memory footprint has been reduced from 204 MB to 44 MB

Note : The idea is to convert the input parameter rather than converting the table column

Case 2) Effect of If Statements in Calculated columns

In the below example I’m trying to extract the year and month from date into a particular format..

Ex: Convert 20150108 to 01/2015

Option1 ) : I will use the if statement in calculated column as

if(“EDATU”=”,”,midstr(“EDATU”,5,2)+‘/’+leftstr(“EDATU”,4))

Option2) Without If Statement

midstr(“EDATU”,5,2)+‘/’+leftstr(“EDATU”,4)

Option1)

SELECT TOP 200 “DT_EDATU”, “EDATU”

FROM “_SYS_BIC”.”system-local.private.Temp.perf/INPUTDATE4″

Statement ‘SELECT TOP 200 “DT_EDATU”, “EDATU” FROM “_SYS_BIC”.”system-local.private.Temp.perf/INPUTDATE4″‘

successfully executed in 722 ms 44 µs (server processing time: 710 ms 127 µs)

Option2)

SELECT TOP 200 “DT_EDATU2”, “EDATU”

FROM “_SYS_BIC”.”system-local.private.Temp.perf/INPUTDATE4″

Statement ‘SELECT TOP 200 “DT_EDATU2”, “EDATU” FROM “_SYS_BIC”.”system-local.private.Temp.perf/INPUTDATE4″‘

successfully executed in 27 ms 789 µs (server processing time: 15 ms 559 µs)

Results: As you can see we have a runtime improvement of approx 30 times.

Now let’s analyze the memory utilization of both the options – I pulled this from Viz Plan

Option1)

Option2)

Option1                                                                                                                                                                Option2

In Option2 the filter is push down compared to option1

Also note in Option1 – The IF clause is converted to case statement….

Explanation of common operators in Plan Viz

Case 3) Reading data from input parameter

Input Parameter : IP_Material with Default Value ALL

IP_Material = ALL will return all records

IP_Material = 123 will return 1 records

Filter Expression = (“MATNR”= ‘$$IP_Material$$’   OR ‘ALL’=’$$IP_Material$$’)

Input Parameter : IP_Material with Multiple entries and Default Value ALL

IP_Material = ALL will return all records

IP_Material = ”’123”,”456”’will return 2 records

Filter Expression = (in(“MATNR”,$$IP_Material$$) or in(‘ALL’,$$IP_Material$$ ))

Note :  I would recommended to stay away from Match and *

Thank you all and looking forward for your valuable feedback ..

Next time I will try to come up with a blog for How to interfere the execution plan using the standard SQL HINTS.