select句にSQLを書く場合とJOINする場合の比較

• MySQL

(8.0.26)

-- 1. テストデータ作成

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
   );

drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 bigint
   );

drop procedure proc1;

delimiter //
create procedure proc1(in x int)
begin
  declare i int;
  set i = 0;
  start transaction;
  while i < x do
    set i = i + 1;
    insert into tab1 values(
     i
    ,floor(rand() * 1000)+1
    );
    insert into tab2 values(
     i
    ,floor(rand() * 1000)+1
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);

select count(*) from tab1;
select * from tab1 order by rand() limit 20;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);

select count(*) from tab2;
select * from tab2 order by rand() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);

analyze table tab1;
analyze table tab2;

-- 2. 比較

-- ①select句に書く場合
explain
select t1.col1,(select count(*) from tab2 t2 where t2.col2 = t1.col2) cnt
from tab1 t1
;

time mysql test < a1.sql > /dev/null

real    2m21.748s

-- ②joinする場合

explain
select t1.col1,coalesce(t2.cnt,0) cnt
from tab1 t1
left join (select col2,count(*) cnt from tab2 group by col2 ) t2
on t2.col2 = t1.col2
;

time mysql test < a2.sql > /dev/null

real    0m0.944s

サブクエリはselect句に書くよりもjoinするほうが速い

• Oracle

(19c)

-- 1. テストデータ作成

drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 int
  );

drop table tab2 purge;
create table tab2(
    col1 int 
   ,col2 int
   );

declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
    i
   ,floor(dbms_random.value(1, 1001))
   );
  
  insert into tab2 values(
    i
   ,floor(dbms_random.value(1, 1001))
   );
end loop;
end;
/

commit;

select count(*) from tab1;
select * from tab1 order by dbms_random.value()  fetch first 20 rows only;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);

select count(*) from tab2;
select * from tab2 order by dbms_random.value()  fetch first 20 rows only;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);

set time on
set timing on

exec dbms_stats.gather_table_stats(user,'TAB1');
exec dbms_stats.gather_table_stats(user,'TAB2');

-- 2. 比較

-- ①select句に書く場合

explain plan for
select t1.col1,(select count(*) from tab2 t2 where t2.col2 = t1.col2) cnt
from tab1 t1
;

select * from table(dbms_xplan.display(format=>'ALL') );

time sqlplus test/test@pdb1 < a1.sql > /dev/null

real    0m37.046s

-- ②joinする場合

explain plan for
select t1.col1,nvl(t2.cnt,0) cnt
from tab1 t1
left join (select col2,count(*) cnt from tab2 group by col2 ) t2
on t2.col2 = t1.col2
;

select * from table(dbms_xplan.display(format=>'ALL') );

time sqlplus test/test@pdb1 < a2.sql > /dev/null

real    0m21.045s

サブクエリはselect句に書くよりもjoinするほうが速い

• PostgreSQL

(14)

-- 1. テストデータ作成

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 bigint
  );
drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 bigint
   );
   

start transaction;
insert into tab1 select
   g
  ,floor(random() * 1000)+1
from generate_series(1,1000000) g;

commit;

start transaction;
insert into tab2 select
   g
  ,floor(random() * 1000)+1
from generate_series(1,1000000) g;

commit;

select count(*) from tab1;
select * from tab1 order by random() limit 20;
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);
select count(*) from tab2;
select * from tab2 order by random() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);

\timing 1

analyze tab1;
analyze tab2;

-- 2. 比較

-- ①select句に書く場合
explain analyze
select t1.col1,(select count(*) from tab2 t2 where t2.col2 = t1.col2) cnt
from tab1 t1
;

Execution Time: 100387.650 ms

-- ②joinする場合

explain analyze
select t1.col1,coalesce(t2.cnt,0) cnt
from tab1 t1
left join (select col2,count(*) cnt from tab2 group by col2 ) t2
on t2.col2 = t1.col2
;

Execution Time: 438.785 ms

サブクエリはselect句に書くよりもjoinするほうが速い

• SQL Server

(2019)

-- 1. テストデータ作成

drop table tab1;
create table tab1(
    col1 bigint not null
   ,col2 bigint
   );

drop table tab2;
create table tab2(
    col1 bigint not null
   ,col2 bigint
   );

set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
  insert into tab1 values(
     @i
    ,floor(rand() * 1000)+1
    );
   insert into tab2 values(
     @i
    ,floor(rand() * 1000)+1
    );
 set @i = @i + 1;
end
commit;

select count(*) from tab1;
select top 20 * from tab1 order by newid();
alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);

select count(*) from tab2;
select top 20 * from tab2 order by newid();
alter table tab2 add constraint tab2pk primary key (col1);
create index ind22 on tab2(col2);

set statistics time on

update statistics tab1;
update statistics tab2;

-- 2. 比較

-- ①select句に書く場合
select t1.col1,(select count(*) from tab2 t2 where t2.col2 = t1.col2) cnt
from tab1 t1
;

経過時間 = 3188 ミリ秒。

-- ②joinする場合

select t1.col1,coalesce(t2.cnt,0) cnt
from tab1 t1
left join (select col2,count(*) cnt from tab2 group by col2 ) t2
on t2.col2 = t1.col2
;

経過時間 = 3151 ミリ秒。

※SQL Serverの場合、ほぼ同じ実行計画となり、どちらの書き方でも実行時間は変わらない