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

"What do I need before I start?"

" This tutorial is designed for SAP HANA on premise and SAP HANA, express edition. It is not designed for SAP HANA Cloud. Tutorials: Creating Stored Procedures "

Parallel Processing and Parameters

Leveraging SQLScript in stored procedures, user-defined functions, and user-defined libraries.

🎓intermediate ⏱15 min SAP HANA Intermediate Sql Express Edition 0/5

Overview

🎓 intermediate ⏱ 15 min. SAP HANAIntermediateSqlExpress Edition

You will learn

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

R Rich Heilman November 1, 2022

Created by September 5, 2016

Contributors

Prerequisites

This tutorial is designed for SAP HANA on premise and SAP HANA, express edition. It is not designed for SAP HANA Cloud.
Tutorials: Creating Stored Procedures

Steps

Open all Close all

Intro

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.

Step 2 Assign SELECT statements

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

The completed code should be similar to this.

Code

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

Step 3 Save and build