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
