HANA - Scalar Function not executing in parallel

[former_member235648]

Hi,

My cenario is:

1. 1 Graph Calc view calling
2. 1 SQLScript View that execute a SQL with 2 scalar function

I have 2 ScalarFunction in HANA to execute some complex-logic for a data range. I execute both separately and time results are:

select value, Scalar_Function1(value) from table

--> 14sec

select value, Scalar_Function2(value) from table

-->16sec

So, if I try execute the both ScalarFunction on same SQL the result time is:

select value, Scalar_Function1(value), Scalar_Function2(value) from table

-->30sec

I think hana is not running this SQL statement in parallel mode. What is necessary to do for parallel execution of this Functions?

Thanks

[lbreddemann]

what Hana version? What does the explain plan looking like? How many parallel threads provide your system?

[former_member235648]

Hi,

The HANA version is "SAP HANA SPS 10 Database Maintenance Revision 102.01"

About the thread system configurations this is the propertis defined:

1. max_comm_thread = 20
2. max_pop_thread = 32
3. max_server_pop_thread = 100

Explain does not show me much. It looks like he's not showing me the explain of my function.

I modified the functions to try to understand why they were so slow. Within the function only the following SQL runs:

Select 1 from dummy;

The function is executed for 6248 record, and the -Memory Allocated is 355 GB.

[lbreddemann]

Alright, I had a quick look into this in my instance (SPS11) and to me, it seems that the scalar functions get executed together in the single JECalculate POP. There doesn't seem to be an option for parallel processing within the function, so it's feeding whatever goes into the POP through this single lane.

To improve on that, you might want to look into table functions or plain SQL instead as these can operate on whole sets at once instead of single records.

[BenedictV]

Just a suggestion, since I haven't tried it out myself.

Scalar functions allow multiple parameters. You could check if a "function(value1, value2)" would execute faster than two separate function calls.

[former_member235648]

Hi,

Yes, I can put more then 1 parameter, but Scalar Function returns only one value. I have two Scalar Function because I need two different values.

[former_member235648]

Lars,

But it's not at all interesting for a database to run Scalar Function in sequential mode. This is not good and leaves the perfromance too low.

About use Table Function or plain SQL.

1. Plain SQL not solve my problem. I have a medium complex logic to execute. I could not transcribe my need into a single plain SQL.
2. I created Table Function with the required logic using ARRAYS to store data and return a table with the values I need. However, the performance of a Table Function using ARRAYS and internal SQL queries is very very poor.

I am using ARRAYS because I can not use DML into Calculation SQLScript based or in a Table Funcion.

[BenedictV]

Hello Vitor, since you are in SPS10, I am sure you can use scalar functions to return multiple values. You can call it like: Select function1(param1,NULL).return1, function1(NULL,param2).return2 from table;

I just used simple selects from DUMMY, so I am not able to check how it performs comparatively. But you can give it a try.

[lbreddemann]

There are many ways to answer this comment, pointing out that we haven't seen your code so far, or that you haven't mentioned anything about data volumes and expected performance, or that there are many successful implementations on HANA that do complex computations on large datasets really quick (indicating that there might be a better way to solve the issue than your approach) - but the most striking point of your comment is that there is no further question in it. Just the statement that your code runs too slow on HANA. With that statement alone, there is nothing I can do for you.

[former_member235648]

Hi Lars,

I understand your point.

I will consider that use of Scalar Functions will not solve my problem because is not possible run on or more function in parallel.

[lbreddemann]

Fine with me.