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
.
-
Define two tabular output parameters which will be used to explicitly pass the results of the SELECT statements to the caller.
-
Next, assign SELECT statements to the output parameters as shown here.
-
The completed code should be similar to this.
PROCEDURE "get_po_header_data"(
OUT EX_PO_CREATE_CNT TABLE(
CREATE_CNT INTEGER,
"CREATEDBY" NVARCHAR(255)),
OUT EX_PO_CHANGE_CNT TABLE(
CHANGE_CNT INTEGER,
"MODIFIEDBY" NVARCHAR(255)) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
ex_po_create_cnt = SELECT COUNT(*) AS CREATE_CNT, "CREATEDBY"
FROM "OPENSAP_PURCHASEORDER_HEADERS" WHERE ID IN (
SELECT "POHEADER_ID"
FROM "OPENSAP_PURCHASEORDER_ITEMS"
WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
GROUP BY "CREATEDBY";
ex_po_change_cnt = SELECT COUNT(*) AS CHANGE_CNT, "MODIFIEDBY"
FROM "OPENSAP_PURCHASEORDER_HEADERS" WHERE ID IN (
SELECT "POHEADER_ID"
FROM "OPENSAP_PURCHASEORDER_ITEMS"
WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
GROUP BY "MODIFIEDBY";
END
Step 2: Save, deploy, run and check results
-
Save the procedure.
-
Perform a Deploy
-
Use what you have learned and return to the Database Explorer page and run the procedure again.
-
The CALL statement will be inserted into a new “SQL” tab. Click the Run button
-
Check the execution time again, you may notice that it is a bit faster this time depending on your system. The reason is that these SQL statements are now executed in parallel.