How to write SQL queries to run in Parallel

[former_member201560]

Dear all,

It would be great help if you can provide some inputs on how to write SQL functions/procedures to run in parallel. For example, need to write stored procedures or functions to run for more than 10000 articles within 2 to 3 minutes as per business expectations. The business logic involves lot of checks and validations based on article type.

Please help me out how to take it forward on writing SQL queries insde stored procedures/functions to run in parallel.

Regards,

Yogesh

[lbreddemann · Accepted solution]

HI Yogesh

There is nothing you need to specifically do in order to gain parallel processing in SAP HANA.

Even a single SQL command will internally split up into sub-tasks that are then executed in parallel if possible.

For stored procedures you need to keep an eye on not preventing parallel processing, e.g. by using write-enabled procedures or cross-engine development (CE-functions and SQL mixed in one procedure).

You may consider table partitioning as a way to employ larger factors of parallelism, but beware that this is a rather big change and that it will affect all coding that accesses the partitioned tables.

Beyond these things, there are only few means to directly influence parallelism. It's really an automatic feature of the SAP HANA environment.

- Lars

[former_member201560 · Accepted solution]

Hi Lars,

Thanks for your reply. You mean to say that for parallel processing, we should not write stored procedures with write enabled OR should not use CE and SQL functions mixed  ??

I could not able to understand, can you please provide some inputs on this please?

Regards,

Yogesh

[lbreddemann · Accepted solution]

Yogesh

All this information is included in the developers guide and it is really recomendable to read this before starting developing solutions on SAP HANA.

Some types of parallelization obviously have to be stopped with allowing data changes (else you could create write inconsistencies).

And when mixing CE functions and SQL code, data eventually has to be passed betweeen execution engines and certain pre-execution optimizations cannot take place.

- Lars

[former_member201560 · Accepted solution]

Hi Lars,

Per our business logic I have to write write-enabled stored procedures that involves cursors, temporary tables (without CE functions).

Is there any specific parameter that has to be focused so that default parallel processing is not prevented.

It will be of great help if you can provide some inputs on how to influence parallel processing directly.

Thanks for your time and responses.

Regards,

Yogesh

[thomas_jung · Accepted solution]

There are certain rules within SQLScript which you should follow if you want to optimize your code for parallelization.

Some things to keep in mind about parallel processing are statements will be executed in parallel unless any of the following occur: Any local scalar parameters or variables are used in the procedure, if any read/write procedures or DML/DDL operations are executed within the procedure, if any imperative logic is used, or if any SQL statements are used which are not assigned to a variable.