Skip to Content
avatar image
Former Member

hana cursor low performance in our prd system

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Apr 14, 2016 at 02:15 PM

    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

    Add comment
    10|10000 characters needed characters exceeded