Skip to Content

Parallel Processing and Parameters

test
0 %
Parallel Processing and Parameters
Details

Parallel Processing and Parameters

July 17, 2020
Created by
September 5, 2016
Leveraging SQLScript in stored procedures, user-defined functions, and user-defined libraries.

You will learn

  • How to take full advantage of parallel processing in SAP HANA by using table variables

Prerequisites

In this exercise, we will modify the code of procedure get_po_header_data so that it takes full advantage of the parallel processing within HANA by using table variables.


Step 1: Edit previous procedure

Return to your procedure called get_po_header_data.

Existing Procedure

Define two tabular output parameters which will be used to explicitly pass the results of the SELECT statements to the caller.

Define output
Log on to answer question
Step 2: Assign SELECT statements

Next, assign SELECT statements to the output parameters as shown here.

assign select

The completed code should be similar to this.

PROCEDURE "get_po_header_data"(
    OUT EX_PO_CREATE_CNT TABLE(
       CREATE_CNT INTEGER,
      "HISTORY.CREATEDBY.EMPLOYEEID" NVARCHAR(10)),
    OUT EX_PO_CHANGE_CNT TABLE(
       CHANGE_CNT INTEGER,
       "HISTORY.CHANGEDBY.EMPLOYEEID"  NVARCHAR(10)) )  
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   READS SQL DATA AS
BEGIN

ex_po_create_cnt = SELECT COUNT(*) AS CREATE_CNT, "HISTORY.CREATEDBY.EMPLOYEEID"
     FROM "PO.Header" WHERE PURCHASEORDERID IN (
                     SELECT PURCHASEORDERID
                          FROM "PO.Item"
          WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
GROUP BY  "HISTORY.CREATEDBY.EMPLOYEEID";

ex_po_change_cnt = SELECT COUNT(*) AS CHANGE_CNT, "HISTORY.CHANGEDBY.EMPLOYEEID"
     FROM "PO.Header"  WHERE PURCHASEORDERID IN (
                     SELECT PURCHASEORDERID
                          FROM "PO.Item"
          WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
GROUP BY  "HISTORY.CHANGEDBY.EMPLOYEEID";

END
Log on to answer question
Step 3: Save and build

Save the procedure.

Save Procedure

Perform a build on the hdb module.

Build Module
Log on to answer question
Step 4: Run the procedure again

Return to the Database Explorer page and run the procedure again.

HRTT

The CALL statement will be inserted into a new “SQL” tab. Click the Run button

Run
Log on to answer question
Step 5: Check execution time

Check the execution time again, you may notice that it is a bit faster this time. The reason is that these SQL statements are now executed in parallel.

Check execution time
Log on to answer question

Next Steps

Back to top