Parallelization <=> serialization, when parallelization does not work?

[krisztian_szabo1]

Hi

The following are preventing parallel execution as far as I know:

Scalar parameters or variables used within procedure

SQL not assigned to variable

Has imperative and loop logic
Has DDL operations
operations that serialize (CASE )

In the following example temp1 and temp 2 can run parallel with Intra-Query Parallelism as well? temp3 will need the result of temp1 and temp2 so I quess no Inter-query Parallelism but what about Intra-Query Parallelism,in this point does HANA drop from the parallelization phase or temp 3 will be also run parallel just had to wait the result of temp1 and temp2?

What about  itab_all_bp  this will not run Intra-Query Parallelism because of the Case?

What are the other commands which serialize other than the Case?

Waiting for your valuable input.

Krisztian

[lbreddemann · Accepted solution]

Hi there,

you're mixing things up here.

Intra-query parallelism is not influenced by the SQLScript features used. It refers to the operations performed for one single SQL command (scanning, filtering, projecting, joining, etc.). There is no direct way for you to control that. SAP HANA will always try to execute the statements as fast as possible.

However, the inter-query parallelism is heavily influenced by what commands you use and the dependencies between the queries in your script.

In your example itab_all_bp  depends on temp3 which in turn depends on temp1 and temp2.

So the execution graph would allow temp1 and temp2 to be executed in parallel, the result set will be fed into temp3 (maybe this could be transformed into a outer query and sub-selects internally, but to be sure you need to use planviz here).

Finally itab_all_hp takes the result set from temp3 and works on that.

So, yes, you do get parallelism here.

- Lars

[krisztian_szabo1 · Accepted solution]

Hi

Thnk you! It was not clear if intra query parallelism cannot be influenced. What about the serialization and inter query parallelization? Which are the commands aparts from CASE which prevents it?

Br

Krisztian

[lbreddemann · Accepted solution]

The documentation lists the operations that trigger a sequential execution of commands within a SQLScript procedure.

If you have the feeling that something should run more in parallel, it's best to look at the planviz first to get an actual picture of what happens.

Of course you want to avoid limiting the execution options for your code upfront, but typically you will have to check the actual execution anyway to make sure not to have missed anything.