Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
amol_samte
Contributor
5,714
Hello All,

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

https://blogs.sap.com/2014/01/22/abap-managed-database-procedures-introduction/

Now lets see how to add in AMDP.

  1. I am selecting PRICE value from SFLIGHT table/ CARRNAME value from SCARR table and taking into variable to update in custom table(ZTABLE1, ZTABLE2)

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

Execution Plan:



 

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 :

  1. DML

  2. Imperative logic

  3. Autonomous transaction

  4. Implicit SELECT and SELECT INTO scalar variable


Restrictions and Limitations

  1. Modification of tables with a foreign key or triggers are not allowed

  2. Updating the same table in different statements is not allowed

  3. Only concurrent reads on one table are allowed. Implicit SELECT and SELCT INTO scalar variable statements are supported.

  4. Calling procedures containing dynamic SQL (for example, EXEC, EXECUTE IMMEDIATE) is not supported in parallel blocks

  5. Mixing read-only procedure calls and read-write procedure calls in a parallel block is not allowed.



For more details :




you can share you point of view and expert comments.



Cheers 😉


Amol
4 Comments
Labels in this area