cancel
Showing results for 
Search instead for 
Did you mean: 

Execute several "Call" statements in parallel

Former Member
0 Kudos

A procedure (eg PROCEDURE1) has been created in which we're "looping". Within every loop entry, we're calling another procedure (eg. PROCEDURE99)  via the EXEC command.

The example code would be something like this:

exec('call "<schema_name>"."PROCEDURE99"("input_table1", "input_table2", "output_table1") with overview ')

(PROCEDURE99 requires 2 input tables and 1 output table to run correctly)

Instead of 1 massive loop embedded in PROCEDURE1, we'd like to make smaller loop entries by distributing the "loop data" over multiple procedures. (Eg. PROCEDURE2, 3 and 4)

The above approach was successful, but now we're struggling how to call PROCEDURE1,2,3 and 4 in parallel via the CALL command within the SQL console of the HANA studio.

As soon as we're calling procedure1 (via CALL PROCEDURE1(input1, input2, output1)) we have to wait until PROCEDURE1 is finished, before PROCEDURE 2 will be called...and so on.

Is there a way to execute the 4 procedures in parallel? If so: What is the correct SQL(script) code to use?

Thanks in advance for your answer(s)

Grt

Sven

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member182114
Active Contributor
0 Kudos

Hi Sven,

First of all, in HANA it's also better rething the need of procedure and loops in favor of mass access and set operations. In my experience it may appears a bit dificult on the beggining but tremendously beneficial later.

It's automatic put in parallel if READS SQL DATA and no imperative logic used.

Using exec also made it works sequentially.

One option you can explore is Script Calculation View, but you have the problem of input tables which is not possible on CV, because this I used the term explore. Maybe you can do part dynamic and part static with high performance.

Regards, Fernando Da Rós

Former Member
0 Kudos

We had a similar challenge. Our approach was to generate a dynamic procedure. This procedure would need to be defined as "READS SQL DATA".

It can't do any looping (or other "Imperative Logic as defined in the SQLScript reference) - simply your CALL statements to your other procedures, which must all also be defined as "READS SQL DATA". The procedure also can't update any database objects (do inserts/updates/deletes, etc.).

If you can meet all these restrictions, then the optimizer, as I understand it, should be able to parallelize the call statements, assuming that it doesn't find any conflicts that would cause it to serialize the requests. There's no mechanism to explicitly execute statements in parallel.

former_member182114
Active Contributor
0 Kudos

Hi Terry,

You will create one or all the procedures dynamically? Or are you saying that the call to procedures are dynamic?

Please provide more details of your scenario. I'm currently choosing stay as most as static as possible and not paying with performance things.

One trick I used before is put the calling code inside script calculation view and run a single sql statement with union all among many select's. It's something like put CV in parallel and procedures in consequence...

Regards, Fernando Da Rós

Former Member
0 Kudos

Hi Fernando,

We are doing specialized profiling, and can be asked to profile any arbitrary table/view, so the schema for each is different. We iterate through the columns in the input object, and from that dynamically generate a procedure that will call the method that will profile each column in parallel. This procedure is then called dynamically (because it has to be uniquely named).

It's all a temporary solution, as we're working to get our core components working as custom calc nodes and a calc view, somewhat like you describe.

Regards,

Terry

former_member182114
Active Contributor
0 Kudos

Hi Terry,

Thanks for the background.

In this case I'd say to use the dynamic approach just to create an static code which everything need to run in parallel.

Is it possible to save the input data into temporary tables and the code on "new" procedure look for it?

Or even better, Is it possible to you create a performatic procedure without input parameters, loading data from table, and call it from a dynamic procedure which convert the input data to this table?

Regards, Fernando Da Rós