In this blog we are going to understand how to achieve parallelization in AMDP.
In very simple terms, parallelization is to break the main task into smaller units and simultaneously execute them to achieve the results.
Earlier DML statements and read-write procedure calls had to be executed sequentially, but now it is possible to parallelize the execution of independent DML statements and read-write procedure calls by using parallel execution blocks.
Below is syntax can be applicable in both AMDP and Native HANA Procedure
Here is syntax
BEGIN PARALLEL EXECUTION <Stmt>... END;
Before going towards to the example, have a look on basics of AMDP
Now lets see how to add in AMDP.
- I am selecting PRICE value from SFLIGHT table/ CARRNAME value from SCARR table and taking into variable to update in custom table(ZTABLE1, ZTABLE2)
- Updating PRICE and CARRNAME in custom table.
METHOD parallel BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING sflight scarr ztable1 ztable2. DECLARE lv_price INTEGER; DECLARE lv_airline_name nvarchar(20); select top 1 price into lv_price from sflight where carrid = 'AA'; select top 1 carrname into lv_airline_name from scarr where carrid = 'AA'; BEGIN PARALLEL EXECUTION UPDATE ztable1 SET price = lv_price WHERE carrid = 'AA'; UPDATE ztable2 SET carrname = lv_airline_name WHERE CARRID = 'AA'; END; ENDMETHOD.
Just to understand what is structure of custom table and what values been updated, sharing below result set.
Some important things we need to consider while doing parallelization
Following statement are allowed :
- Imperative logic
- Autonomous transaction
- Implicit SELECT and SELECT INTO scalar variable
Restrictions and Limitations
- Modification of tables with a foreign key or triggers are not allowed
- Updating the same table in different statements is not allowed
- Only concurrent reads on one table are allowed. Implicit SELECT and SELCT INTO scalar variable statements are supported.
- Calling procedures containing dynamic SQL (for example, EXEC, EXECUTE IMMEDIATE) is not supported in parallel blocks
- Mixing read-only procedure calls and read-write procedure calls in a parallel block is not allowed.