We are using HANA version 1.00.70.

I wondering SQL procedure’s “call-performance”.

Below my test statements.
Test shows that direct insert compared procedure call’s insert is about 20 times faster.
HANA insert statement is ~3600; but with procedure call it is only 180!
On standard laptop and SQL Server 2005; both execution takes same time and output is about 1500 events per seconds.

Do you have idea what causes huge difference for executions ?
It looks like HANA makes “execution plans” or something similar everytime procedure has called
(does not have cached plans).

drop table JRE_2.TestLoad;
create column table JRE_2.TestLoad (MyNumber int);

drop procedure JRE_2.MyValueInsert;
create Procedure JRE_2.MyValueInsert(v_MyNumber int)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS 
BEGIN
insert into JRE_2.TestLoad (MyNumber) values (v_MyNumber);
END;

drop procedure JRE_2.InsertTest;
create Procedure JRE_2.InsertTest(v_CallProc tinyint)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS 
BEGIN

declare lv_StartTime datetime :=now();
declare lv_EndTime datetime;
declare lv_LoopNumber int :=0;
declare lv_MethodUsed varchar(16);

if ifnull(v_CallProc,0) = 0 then
  lv_MethodUsed := ‘Insert’;
else
  lv_MethodUsed := ‘Procedure call’;
end if; 

while (lv_LoopNumber <=50000) do
 
  if (lv_MethodUsed = ‘Insert’) then
   insert into JRE_2.TestLoad (MyNumber) values (lv_LoopNumber);
  else
   call JRE_2.MyValueInsert(lv_LoopNumber);
  end if;
 
  lv_LoopNumber := lv_LoopNumber + 1;

end while;

lv_EndTime := now();

select lv_MethodUsed as InsertMethod, lv_LoopNumber as Cnt, seconds_between(lv_StartTime,lv_EndTime) as Duration, cast(lv_LoopNumber/seconds_between(lv_StartTime,lv_EndTime) as int) as InsertsPerSeconds
from dummy;

END;

/* Tests for insert statement */

drop table JRE_2.TestLoad;
create column table JRE_2.TestLoad (MyNumber int);
call JRE_2.InsertTest(0);

==> Results:
INSERTMETHOD; CNT; DURATION; INSERTSPERSECONDS
Insert;   50001; 13;   3 846
Insert;   50001; 14;   3571

/* Test for procedure calls  */
drop table JRE_2.TestLoad;
create column table JRE_2.TestLoad (MyNumber int);

call JRE_2.InsertTest(1);

==> Results:
INSERTMETHOD; CNT; DURATION; INSERTSPERSECONDS
Procedure call; 50001; 276;  181
Procedure call; 50001; 277;  180

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply