hana cursor low performance in our prd system

[Former Member]

procedure source code:

------------------------------------------------------------------------------------------------------------------------------------------------------------------

create procedure "TEMP"."sp_cursorperformancetest"()

    language sqlscript

    sql security definer

    default schema "TEMP"

as

begin

   -------------------------------------- cursor performance test---------------------------------------------

   declare v_mo_id varchar(12);

   declare v_index int := 0;

   --1.define cursor

   declare cursor cursor_1 for

   select

         productionordid mo_id,

         bomname bomname,

         mo_type mo_type,

         mo_qty mo_qty,

         ud_storage_condition item_type,

         res_name production_line,

         releasedate releasedate,

         itemcategory itemcategory,

         werks werks,

         matnr matnr,

         bom_qty qty,

         LOCATION storage_location,

         physical_plant physical_plant,

         remark remark,

         vendor lifnr,

         posnr,

         no_cut_back,

         process_count,

         BUILD_ENTITY

   from tmp_mo_line;

   --2. exception

   declare exit handler for sqlexception

   begin

        insert into runtime_info ( step, remark, start_time )

        values ( 0, '' , current_timestamp);

   end;

   --3. loop cursor

   for cur_mo as cursor_1 do       

        v_mo_id := cur_mo.mo_id;

        v_index := :v_index + 1;

        insert into runtime_info ( step, remark, start_time )

        values ( :v_index, :v_mo_id, current_timestamp );

        exec 'commit';

   end for;

end;

----------------------------------------------------------------------------------------------------------------------------------

1. hana server sps10

2. tmp_mo_line records = 8000, runtime_info table is empty before running

3. no other programe refer the 2 tables

4. call "TEMP"."sp_cursorperformancetest"(),

in dev system, successfully executed in 65 s, but in prd , it takes about 8 minutes

may be this a system level problem, i think, can someone tell me how to optimize performance?

thanks in advance.

[Bojan-lv-85]

rewrite the code to simple SQL and compare the performance. If you observe the same performance issues I would suggest following the documentation which is already there, e.g.:

2000002

FAQ: SAP HANA SQL Optimization

BR, Bojan