Skip to Content
-1

Cursor in Hana - too slow

Jun 05, 2017 at 11:35 AM

195

avatar image

Hello,

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;

END FOR;

CLOSE c_read;

var_out = select * from var_Exit;

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Florian Pfeffer
Jun 05, 2017 at 05:08 PM
2

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.

Regards,
Florian

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thanks very much Florian ^^

In the logic, I read a row and save it on variables that are used in the next row to do comparison,

something like: if field1.previous_value <> field1.current_value, then set field2.value = 0 .

here part of the code:

FOR cur_row as c_read DO

-- read current values

v_PriceSvc = cur_row.PriceSvc;
v_PriceOrd = cur_row.PriceOrd;

-- IF logic to assign new values

if cur_row.idpgt = prev_idpgt then
v_PriceOrd = 0;
if cur_row.idpgtemi = prev_idpgtemi then

....other similar IF...

end if;

-- Set current values as previous values to be used in next cycle

prev_idpgt = cur_row.idpgt;

-- Set calculated values in exit

var_exit = select * from :var_Exit
union all
select cur_row.id_ord, .... v_PriceOrd, v_PriceSer, cur_row.idpgt.... from dummy;

END FOR;

0

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).

1
Ilaria Spina Jun 06, 2017 at 12:01 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Sounds like that this would have been a job for window functions...

0