Question Re: HANA - Scalar Function not executing in parallel in Technology Q&A

HANA - Scalar Function not executing in parallel

former_member235648 — Thu, 30 Mar 2017 11:18:43 GMT

Hi,

My cenario is:

1 Graph Calc view calling
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

Re: HANA - Scalar Function not executing in parallel

lbreddemann — Thu, 30 Mar 2017 12:04:26 GMT

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

Re: HANA - Scalar Function not executing in parallel

former_member235648 — Thu, 30 Mar 2017 19:44:20 GMT

Hi,

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

About the thread system configurations this is the propertis defined:

max_comm_thread = 20
max_pop_thread = 32
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.

Re: HANA - Scalar Function not executing in parallel

lbreddemann — Thu, 30 Mar 2017 22:34:18 GMT

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.

Re: HANA - Scalar Function not executing in parallel

BenedictV — Fri, 31 Mar 2017 05:59:50 GMT

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.

Re: HANA - Scalar Function not executing in parallel

former_member235648 — Fri, 31 Mar 2017 14:38:18 GMT

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.

Re: HANA - Scalar Function not executing in parallel

former_member235648 — Fri, 31 Mar 2017 14:53:45 GMT

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.

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

Re: HANA - Scalar Function not executing in parallel

BenedictV — Fri, 31 Mar 2017 18:00:15 GMT

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.

Re: HANA - Scalar Function not executing in parallel

lbreddemann — Fri, 31 Mar 2017 23:52:10 GMT

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.

Re: HANA - Scalar Function not executing in parallel

former_member235648 — Mon, 03 Apr 2017 11:47:48 GMT

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.

Re: HANA - Scalar Function not executing in parallel

lbreddemann — Mon, 03 Apr 2017 23:34:03 GMT

Fine with me.