Rank Function taking a long time to execute in SAP HANA

[Former Member]

Hi All,

I have a couple of reports with rank function which is timing out/ or taking a really long time to execute, Is there any way to get the result in less time when rank functions are involved?

the following is a sample of how the Query looks,

SQL 1:

select      a.column1,

                b.column1,

                rank () over(partition by a.column1 order by sum(b.column2) asc)

from         "_SYS_BIC"."Analyticview1"         b

                join          "Table1"            a

                  on          (a.column2 = b.column3)

group by  a.column1,

b.column1;

SQL 2:

select    a.column1,

                b.column1,

                rank () over( order by min(b.column1) asc) WJXBFS1

from         "_SYS_BIC"."Analytic view2"         b

                cross join                "Table 2"               a

where      (a.column2  like '%a%'

and b.column1  between 100 and 200)

group by  a.column1,

                b.column1

when I visualize the execution plan,the rank function is the one taking up a longer time frame. so I executed the same SQL without the rank() or partition or order by(only with Sum() in SQL1 and Min() in SQL 2) even that took a around an hour to get the result.

1.Does anyone have an any idea to make these queries to execute faster?

2. Does the latency have anything to do with the rank function or could it be size of the result set?

3. is there any workaround to implement these rank function/partition inside the Analytic view itself? if yes, will this make it give the result faster?

Thank you for your help!!

-Gayathri

[henrique_pinto]

when I visualize the execution plan,the rank function is the one taking up a longer time frame. so I executed the same SQL without the rank() or partition or order by(only with Sum() in SQL1 and Min() in SQL 2) even that took a around an hour to get the result.

So, basically you're saying that it's the join that's taking a long time?

[Former Member]

For the SQL 2, the Out of Memory exception is occurring after a 2hr 30 mins (approx). when I visualize the plan , cost of the Join is considerably less compared to rank(), but when executing it without the rank() I am getting a Result but still it is taking an hour.

I am not sure the problem is with the join because this is not the only query in the report with Cross join/ join. Those queries are running fine within a minute or so.

only difference between those queries and these are rank(). So not sure why it is taking so long to execute.

-Gayathri

[Former Member]

Hi Gayathri,

We have a similar requirement in the current project and I have used RANK() in a script-based calculation view. The query runs fast in around 40s, but the CPU usage is toughing 90%.

Did the graphical view workaround help in your case?

Thanks,

Remya

[former_member182302]

Hello There,

Can you try with the approach mentioned in the blog here. Should be the best way to see which one performs better

Regards,

Krishna Tangudu

[Former Member]

Krishna,

I tried both of them, Graphical and CE function,

It is also taking a long time to execute

Graphical view giving me the following error after 2 hr and 36 minutes

Could not execute 'SELECT ORDER_ID,ITEM_ID,RANK from "_SYS_BIC"."EMMAPERF/ORDER_FACT_HANA_CV" group by ...' in 2:36:23.411 hours .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] executor: plan operation failed

CE function - I aborted after 40 mins

Do you know the syntax to declare local variable to use in CE function?