Skip to Content

Cursor in Hana - too slow


a calculation reads the output of another calculation (that is quite fast... about 20 seconds), puts them into a cursor, use the cursor in a FOR cycle where every row is read and then written in the output variable.

The problem is that this new calculation is very slow... it took about an hour.

What can be the problem? Rows and columns of the first calculation are many (about 20 cols and 40.000 rows).

Thanks for any idea!

DECLARE CURSOR c_read FOR ( select * from <calculation_name> ) ;

var_Exit = select null as field1, null as field2 from dummy;

FOR cur_row as c_read DO

... some simple logic in which cursor is read ...

var_exit = select * from :var_exit
union all
select cur.row.field1, cur_row.field2 from dummy;


CLOSE c_read;

var_out = select * from var_Exit;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 05, 2017 at 05:08 PM

    As the Best Practices for SQLScript - Avoid Using Cursors chapter in the official documentation says, cursors should be avoided whenever possible. As you do not share your "simple logic" executed within the cursor loop no clear recommendations can be made if it would be possible to replace your cursor logic with pure SQL logic or maybe a usage of the Map Merge operator makes sense.

    Maybe you can check if you are able to refactor your logic with pure SQL or the Map Merge operator or you share more details.


    Add comment
    10|10000 characters needed characters exceeded

    • If you need to access the previous/following row I would check if it is possible to implement your logic using the HANA window functions LAG and LEAD (depending on that if you wanna check the previous row or the next row).

  • Jun 06, 2017 at 12:01 PM

    I resolved by SQL... I created a sequence to add an incremental ID to calculation output, and then I joined this output with itself on seq = seq-1, so that I can compare current and previous row and set the field values.

    Add comment
    10|10000 characters needed characters exceeded