Skip to Content
Technical Articles

Rank Function performance issue in HANA

This blog is about to discusses the issues identified due to the usage of RANK node in SAP HANA modeling & provide solutions to achieve the same result with different approaches with less time.

 

SAP HANA has graphical based approach to create RANK node in calculation view which usually takes very less time to develop but some time it cause performance issue in production on high data volume

So I would recommend whenever data volume is high then crate Table function and use ROW_NUMBER() instead of rank function. I will drastically improve the performance of the calculation view.

 

Graphical based RANK function in calculation view it will take more time.

Below is the example where I have used 2 Rank function which is taking more time during data preview.

 

Output for Graphical based Rank Function.

 

 

Scripted based table function which will give better performance of the calculation view. Below is the sample code as an example.

 

CREATE FUNCTION “NA_PLM”.”MY.NA.PLM.INTERNAL::TF_BOM_PERFORMANCE_TEST” ( )

RETURNS TABLE

(

“SUBID” NVARCHAR(12),

“IDENT” NVARCHAR(132),

“MATNR” NVARCHAR(18),

“AENNR” NVARCHAR(18),

“VALID_TO” NVARCHAR(32)

“MATERIAL” NVARCHAR(18),

“COMPAVG” DECIMAL(10,4),

“COMPLOW” DECIMAL(10,4),

“COMPUPP” DECIMAL(10,4),

“EWAHR” DECIMAL(3),

“ROW_NUM_2” BIGINT )

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

/*****************************

Write your function logic

*****************************/

VAR_OUT = SELECT

“SUBID”,

“IDENT”,

“MATNR”,

“AENNR”,

“VALID_TO”,

“MATERIAL”,

sum(“COMPAVG”) AS “COMPAVG”,

sum(“COMPLOW”) AS “COMPLOW”,

sum(“COMPUPP”) AS “COMPUPP”,

sum(“EWAHR”) AS “EWAHR”,

ROW_NUMBER() OVER(PARTITION BY “POSNR”,”SUBID”,”MATNR”, ORDER BY “VALID_TO” DESC) AS “ROW_NUM_2”

from

(

SELECT

“SUBID”,

“IDENT”,

“MATNR”,

“AENNR”,

“VALID_TO”,

“MATERIAL”,

sum(“COMPAVG”) AS “COMPAVG”,

sum(“COMPLOW”) AS “COMPLOW”,

sum(“COMPUPP”) AS “COMPUPP”,

sum(“EWAHR”) AS “EWAHR”,

ROW_NUMBER() OVER(PARTITION BY “SUBID”,”VALID_TO”,”MATERIAL” ORDER BY “AENNR” DESC) AS “ROW_NUM”

FROM “_SYS_BIC”.MY.NA.PLM.INTERNAL/TF_TEST”

GROUP BY “SUBID”,

“IDENT”,

“MATNR”,

“AENNR”,

“VALID_TO”,

“MATERIAL”

)

Where ROW_NUM=1

Group by

“SUBID”,

“IDENT”,

“MATNR”,

“AENNR”,

“VALID_TO”,

“MATERIAL”

 

RETURN :VAR_OUT;

 

END;

 ………………………………………………………………………………………………..

 

Output:-

 

Graphical based RANK function is taking more time like 15 Dec

 

 

 

ROW_NUMBER() in table function is taking very less time compare to Graphical based RANK function- Its taking only 5 Sec.

 

 

Note  :- In my case, Development system does not have  much data  but still graphical Rank is taking 15 sec so if we move same in production then  it will directly impact performance of the report due to high volume of data.

So this approach will improve performance of the report.

 

 

 

 

Be the first to leave a comment
You must be Logged on to comment or reply to a post.