(5.6)
drop table tab1;
create table tab1(col1 int not null,col2 int,col3 char(100));
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
declare j int;
declare k int;
set i = 1;
set j = 1;
set k = 1;
while i <= 1000000 do
insert into tab1 values(i,j,rpad('A',100,j));
set i = i + 1;
set k = k + 1;
if k > 10000 then
set j = j + 1;
set k = 1;
end if;
end while;
end
//
delimiter ;
call proc1();
alter table tab1 add constraint tab1p primary key (col1);
create index ind12 on tab1(col2);
analyze table tab1;
1.インスタンスレベルでの確認
1.1.リテラルの場合
time for i in `seq 1 10000`; do
echo "prepare stmt1 from 'select * from tab1 where col1=${i}';"
echo "set @i = ${i};"
echo "execute stmt1;"
echo "deallocate prepare stmt1;"
done | mysql -u root -p'Aaa!1234' test > /dev/null
real 0m2.923s
user 0m0.586s
sys 0m0.488s
1.2.バインド変数の場合
time for i in `seq 1 10000`; do
echo "prepare stmt1 from 'select * from tab1 where col1=?';"
echo "set @i = ${i};"
echo "execute stmt1 using @i;"
echo "deallocate prepare stmt1;"
done | mysql -u root -p'Aaa!1234' test > /dev/null
real 0m2.977s
user 0m0.598s
sys 0m0.490s
→実行時間に相違なし
2.セッションレベルでの確認
2.1.リテラルの場合
drop procedure proc2;
delimiter //
create procedure proc2()
begin
declare vSQL varchar(1000);
declare i int;
declare j varchar(1000);
set i = 0;
while i <= 1000000 do
set i = i + 1;
set j = cast(i as char);
select concat('select * from tab1 where col1=',j) into vSQL;
set @q := vSQL;
set @k := i;
prepare stmt1 from @q;
execute stmt1;
deallocate prepare stmt1;
end while;
end
//
delimiter ;
time echo "call proc2()" | mysql -u root -p'Aaa!1234' test > /dev/null
real 1m2.753s
user 0m6.152s
sys 0m3.390s
2.2.バインド変数の場合
drop procedure proc2;
delimiter //
create procedure proc2()
begin
declare vSQL varchar(1000);
declare i int;
declare j varchar(1000);
set i = 0;
while i <= 1000000 do
set i = i + 1;
set j = cast(i as char);
select concat('select * from tab1 where col1=','?') into vSQL;
set @q := vSQL;
set @k := i;
prepare stmt1 from @q;
execute stmt1 using @k;
deallocate prepare stmt1;
end while;
end
//
delimiter ;
time echo "call proc2()" | mysql -u root -p'Aaa!1234' test > /dev/null
real 0m59.911s
user 0m5.990s
sys 0m3.480s
→実行時間に相違なし
(12cR1)
https://www.oracle.com/technetwork/jp/database/articles/shibacho/index-2405696-ja.html
drop table tab1 purge;
create table tab1(col1 int not null,col2 int,col3 char(100));
declare
j integer;
k integer;
begin
j := 1;
k := 1;
for i in 1..1000000 loop
insert into tab1 values(i,j,rpad('A',100,j));
commit;
k := k+1;
if k > 10000 then
j := j+1;
k := 1;
end if;
end loop;
end;
/
create unique index ind11 on tab1(col1);
alter table tab1 add constraint tab1p primary key (col1) using index ind11;
create index ind12 on tab1(col2);
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'TAB1');
1.インスタンスレベルでの確認
1.1.リテラルの場合
time for i in `seq 1 10000`; do
echo "variable i int"
echo "execute :i := ${i}"
echo "select * from tab1 where col1=${i};"
done | sqlplus test/test > /dev/null
real 0m11.432s
user 0m3.411s
sys 0m0.401s
1.2.バインド変数の場合
time for i in `seq 1 10000`; do
echo "variable i int"
echo "execute :i := ${i}"
echo "select * from tab1 where col1=:i;"
done | sqlplus test/test > /dev/null
real 0m1.143s
user 0m1.055s
sys 0m0.203s
→バインド変数化により処理時間が短くなる
2.セッションレベルでの確認
2.1.リテラルの場合
set timin on
declare
r_tab1 tab1%rowtype;
vSQL varchar2(1000);
j varchar2(1000);
begin
for i in 1..10000 loop
j := to_char(i);
vSQL := 'select * from tab1 where col1= ' || j;
execute immediate vSQL into r_tab1;
end loop;
end;
/
経過: 00:00:06.87
2.2.バインド変数の場合
set timin on
declare
r_tab1 tab1%rowtype;
vSQL varchar2(1000);
j varchar2(1000);
begin
for i in 1..10000 loop
j := to_char(i);
vSQL := 'select * from tab1 where col1= ' || ':k';
execute immediate vSQL into r_tab1 using i;
end loop;
end;
/
経過: 00:00:00.18
→バインド変数化により処理時間が短くなる
(9.4)
drop table tab1;
create table tab1(col1 int not null,col2 int,col3 char(100));
do $$
declare
j integer;
k integer;
begin
j := 1;
k := 1;
for i in 1..1000000 loop
insert into tab1 values(i,j,rpad('A',100,j::char(3)));
k := k+1;
if k > 10000 then
j := j+1;
k := 1;
end if;
end loop;
end
$$
;
create unique index ind11 on tab1(col1);
alter table tab1 add constraint tab1p primary key using index ind11;
create index ind12 on tab1(col2);
analyze tab1;
1.インスタンスレベルでの確認
1.1.リテラルの場合
time for i in `seq 1 10000`; do
echo "prepare stmt1(int) as select * from tab1 where col1=${i};"
echo "execute stmt1(${i});"
echo "deallocate stmt1;"
done | psql test > /dev/null
real 0m3.206s
user 0m0.736s
sys 0m0.317s
1.2.バインド変数の場合
time for i in `seq 1 10000`; do
echo "prepare stmt1(int) as select * from tab1 where col1=\$1;"
echo "execute stmt1(${i});"
echo "deallocate stmt1;"
done | psql test > /dev/null
real 0m3.233s
user 0m0.706s
sys 0m0.294s
→実行時間に相違なし
2.セッションレベルでの確認
2.1.リテラルの場合
\timing on
do $$
declare
j varchar(100);
begin
for i in 1..1000000 loop
j := i::varchar;
execute 'prepare stmt1(int) as select * from tab1 where col1=' || j;
execute 'execute stmt1(' || quote_literal(i) || ')';
execute 'deallocate stmt1';
end loop;
end
$$
;
時間: 94182.694 ms
2.2.バインド変数の場合
\timing on
do $$
declare
j varchar(100);
begin
for i in 1..1000000 loop
j := i::varchar;
execute 'prepare stmt1(int) as select * from tab1 where col1=' || '$1';
execute 'execute stmt1(' || quote_literal(i) || ')';
execute 'deallocate stmt1';
end loop;
end
$$
;
時間: 92303.446 ms
→実行時間に相違なし
(2014)
drop table tab1;
create table tab1(col1 int not null,col2 int,col3 char(100));
declare @i int;
declare @j int;
declare @k int;
set @i= 1;
set @j= 1;
set @k= 1;
while (@i <= 1000000)
begin
insert into tab1 values (@i,@j,'A' + left(replicate(convert(varchar,@j),100),99));
set @i = @i + 1;
set @k = @k + 1;
if @k > 10000
begin
set @j = @j + 1;
set @k = 1;
end
end
go
alter table tab1 add constraint tab1p primary key (col1);
create index ind12 on tab1(col2);
update statistics tab1;
1.インスタンスレベルでの確認
1.1.リテラルの場合
drop procedure dbo.proc1;
go
create procedure dbo.proc1(@param1 int)
as
begin
declare @i int;
declare @j nvarchar(100);
declare @vSQL nvarchar(100);
declare @vSQLParm nvarchar(100);
set @i= @param1;
set @j = convert(nvarchar,@i);
set @vSQL = 'select * from tab1 where col1 = ' + @j;
set @vSQLParm = '@col1 int';
execute sp_executesql @vSQL, @vSQLParm, @col1 = @i
end
go
@echo off
echo %TIME%
for /l %%n in (1,1,10000) do (
sqlcmd -d test -b -Q "exec dbo.proc1 %%n" > nul
)
echo %TIME%
exit /b 0
→6分16秒
1.2.バインド変数の場合
drop procedure dbo.proc1;
go
create procedure dbo.proc1(@param1 int)
as
begin
declare @i int;
declare @j nvarchar(100);
declare @vSQL nvarchar(100);
declare @vSQLParm nvarchar(100);
set @i= @param1;
set @j = convert(nvarchar,@i);
set @vSQL = 'select * from tab1 where col1 = ' + '@col1';
set @vSQLParm = '@col1 int';
execute sp_executesql @vSQL, @vSQLParm, @col1 = @i
end
go
@echo off
echo %TIME%
for /l %%n in (1,1,10000) do (
sqlcmd -d test -b -Q "exec dbo.proc1 %%n" > nul
)
echo %TIME%
exit /b 0
→6分54秒
→実行時間に相違なし
2.セッションレベルでの確認
2.1.リテラルの場合
drop procedure dbo.proc1;
go
create procedure dbo.proc1
as
begin
declare @i int;
declare @j nvarchar(100);
declare @vSQL nvarchar(100);
declare @vSQLParm nvarchar(100);
set @i= 1;
while (@i <= 100000)
begin
set @j = convert(nvarchar,@i);
set @vSQL = 'select * from tab1 where col1 = ' + @j;
set @vSQLParm = '@col1 int';
execute sp_executesql @vSQL, @vSQLParm, @col1 = @i
set @i = @i + 1;
end
end
go
echo %TIME%
sqlcmd -d test -b -Q "exec dbo.proc1" > nul
echo %TIME%
→39秒
2.2.バインド変数の場合
drop procedure dbo.proc1;
go
create procedure dbo.proc1
as
begin
declare @i int;
declare @j nvarchar(100);
declare @vSQL nvarchar(100);
declare @vSQLParm nvarchar(100);
set @i= 1;
while (@i <= 100000)
begin
set @j = convert(nvarchar,@i);
set @vSQL = 'select * from tab1 where col1 = ' + '@col1';
set @vSQLParm = '@col1 int';
execute sp_executesql @vSQL, @vSQLParm, @col1 = @i
set @i = @i + 1;
end
end
go
echo %TIME%
sqlcmd -d test -b -Q "exec dbo.proc1" > nul
echo %TIME%
→2秒
→バインド変数化により処理時間が短くなる
