Why Avoid Using Cursors in HANA database


While the use of cursors is sometime required, they imply a row-at-a-time processing. As a
consequence, opportunities for optimizations by the SQL engine are missed. So you should consider
replacing the use of cursors with loops by SQL statements as follows:

Read-Only Access
For read-only access to a cursor consider using simple selects or join:
CREATE PROCEDURE foreach_proc LANGUAGE SQLSCRIPT AS
   val decimal(34,10) := 0;
   CURSOR c_cursor1 FOR
       SELECT isbn, title, price FROM books;
BEGIN

FOR r1 AS c_cursor1 DO
      val := :val + r1.price;
   END FOR;
END;

This sum can also be computed by the SQL engine:
SELECT sum(price) into val FROM books;
Computing this aggregate in the SQL engine may result in parallel execution on multiple CPUs inside
the SQL executor.

Updates & Deletes:

For updates and deletes, consider using the WHERE CLAUSE
CREATE PROCEDURE foreach_proc LANGUAGE SQLSCRIPT AS
   val INT := 0;
   CURSOR c_cursor1 FOR
       SELECT isbn, title, price FROM books;
BEGIN
   FOR r1 AS c_cursor1 DO
      IF r1.price > 50
      THEN
         DELETE FROM Books WHERE isbn = r1.isbn;
      END IF;
   END FOR;
END;

This delete can also be computed by the SQL engine:
DELETE FROM Books WHERE isbn IN
          (SELECT isbn FROM books WHERE price > 50);



Tags: HANA db cursors,


Share this article :

Post a Comment

 
Copyright © 2011. SAP HANA TUTORIALS FREE - S/4 HANA - All Rights Reserved