(5.6)
drop table tab1;
create table tab1(col1 int);
drop procedure proc1;
delimiter //
create procedure proc1(in param1 integer, in param2 integer)
begin
declare i int;
declare k int;
drop table tab1;
create table tab1(col1 int);
set i = 1;
set k = 0;
start transaction;
while i <= param1 do
insert into tab1 values(i);
set i = i + 1;
set k = k + 1;
if k >= param2 then
commit;
start transaction;
set k = 0;
end if;
end while;
commit;
end
//
delimiter ;
call proc1(1000000,1);
→2 min 56.28 sec
call proc1(1000000,10);
→33.02 sec
call proc1(1000000,100);
→16.46 sec
call proc1(1000000,1000);
→13.27 sec
call proc1(1000000,10000);
→12.79 sec
call proc1(1000000,100000);
→12.71 sec
call proc1(1000000,1000000);
→12.93 sec
(12cR1)
drop table tab1 purge;
create table tab1(col1 int);
create or replace procedure proc1(param1 in integer, param2 in integer)
AUTHID CURRENT_USER as
k integer;
begin
execute immediate 'drop table tab1 purge';
execute immediate 'create table tab1(col1 int)';
k := 0;
for i in 1..param1 loop
insert into tab1 values(i);
k := k + 1;
if k >= param2 then
commit;
k := 0;
end if;
end loop;
commit;
end;
/
※AUTHID CURRENT_USER を付与しないとcreate table で「ORA-01031: 権限が不足しています」発生
set time on
set timing on
exec proc1(1000000,1);
→経過: 00:00:49.10
exec proc1(1000000,10);
→経過: 00:00:24.13
exec proc1(1000000,100);
→経過: 00:00:18.81
exec proc1(1000000,1000);
→経過: 00:00:17.69
exec proc1(1000000,10000);
→経過: 00:00:17.14
exec proc1(1000000,100000);
→経過: 00:00:18.05
exec proc1(1000000,1000000);
→経過: 00:00:18.06
(11)
drop table tab1;
create table tab1(col1 int);
create or replace procedure proc1(param1 in integer, param2 in integer)
language plpgsql
as $$
declare
k integer;
begin
drop table tab1;
create table tab1(col1 int);
commit;
k := 0;
for i in 1..param1 loop
insert into tab1 values(i);
k := k + 1;
if k >= param2 then
commit;
k := 0;
end if;
end loop;
commit;
end;
$$;
\timing 1
call proc1(1000000,1);
→04:06.926
call proc1(1000000,10);
→00:31.964
call proc1(1000000,100);
→00:10.714
call proc1(1000000,1000);
→00:07.777
call proc1(1000000,10000);
→00:07.570
call proc1(1000000,100000);
→00:07.383
call proc1(1000000,1000000);
→00:07.399
(2014)
drop table tab1;
create table tab1(col1 int);
drop procedure proc1;
go
create procedure proc1(@param1 integer,@param2 integer)
as
begin
declare @i integer;
declare @k integer;
drop table tab1;
create table tab1(col1 int);
set @i = 1;
set @k = 0;
begin transaction;
while @i <= @param1
begin
insert into tab1 values(@i);
set @i = @i + 1;
set @k = @k + 1;
if @k >= @param2
begin
commit;
begin transaction;
set @k = 0;
end
end
commit;
end
;
set statistics time on
go
exec proc1 1000000,1;
→741秒
exec proc1 1000000,10;
→463秒
exec proc1 1000000,100;
→492秒
exec proc1 1000000,1000;
→497秒
exec proc1 1000000,10000;
→560秒
exec proc1 1000000,100000;
→592秒
exec proc1 1000000,1000000;
→627秒
