Skip to Content

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

Option1 Option2
Execution Time 340ms 38ms
Memory Utilization 203MB 44MB

 

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

  Option1 Option2
Execution Time 710ms 15ms
Memory Utilization 1.1GB 47MB

 

Explanation of common operators in Plan Viz

Operator Name Explanation
ceAggregationPop Aggregation on temporary internal table delegated to the OLAP Engine.
ceConvertDatatypePop Converts the data type of multiple attribute(s) to different data types
ceCustomCppPop Custom function which is not executed in calc engine
ceGnavAggregationPop Grouping set aggregation delegated to the OLAP Engine.
ceJoinPop Join operation on physical tables or temporary itabs.
ceJoinSearchPop Join & Search on persistent tables
ceOlapSearchPop Search on a persistent OLAP view.
ceProjectionPop Performs a projection on the requested View Attributes.
ceQoPop Executes a converted execution model. Delegated to the SQLEngine.
ceSqlPop Executes the specified SQL statement. Delegated to the SQLEngine.
ceTableSearch Search on a physical column store table.
ceUnionPop Performs union all on temporary itabs.

 

 

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.

 

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Nagaraj G

    Hi Venkateshwar,

    Its awesome, thanks for such a useful tips

     

    Below are my observation while implementing Case 1 and Case 3, wanted share:

    For Case 1:

    we can also use expression editor from input parameter to format input entered. I tried this and the approach that you mentioned, I could see PlanViz was showing same for both

     

     

     

    For Case 3:

    It will work for any default value. I mentioned ‘ABC’ as default value and filter expression as (“VBELN”=‘$$test$$’ or ‘ABC’=‘$$test$$’). View was fetching all the records when default value ‘ABC’ is entered.

    1. I wonder how system will understand to fetch entire values?
    2. When I visualize the plan, I could see that ‘HEX Search’ and Explain Plan shows ‘HEX’ Execution Engine is used. Does system use Hex search when filter is applied like this?

    Could you please help me to understand more on this?

     

    Thanks in advance for your help

    Regards,

    Nagaraj

     

    (0) 
    1. Venkateshwar Goli Post author

       

      Hi Nagaraj,

       

      For Case 1

      The problem when you change in Input Parameter is that the User Might get confused ..Also you would have run into problems if the same view is being used in another view ..Again i’m not saying its wrong its just the readability purpose ..The whole idea is to use the input parameter in the conversion vs the Table Field

      For Case 3

      (“VBELN”=‘$$test$$’ or ‘ABC’=‘$$test$$’). View was fetching all the records when default value ‘ABC’ is entered.

      I wonder how system will understand to fetch entire values?

      “VBELN”=‘$$test$$’ or ‘ABC’=‘$$test$$’).  With default value as ABC or when user inputs ABC it would be converted as “VBELN”=‘$$test$$’ or ‘ABC’=‘ABC’).  – so here you’re just fooling the system by saying ABC = ABC which will be true in all scenario’s infact at this point you’re not even looking VBELN Fields ..So when the “VBELN”=‘$$test$$’ or ‘ABC’=‘$$test$$’).   first condition is met it wouldn’t go the the second condition in that case it would just pick one record

      When I visualize the plan, I could see that ‘HEX Search’ and Explain Plan shows ‘HEX’ Execution Engine is used. Does system use Hex search when filter is applied like this?

      Yes , You’re correct

       

      Thanks

      Venkat

      (0) 

Leave a Reply