deleteとinsertの速度比較

Oracleはinsertのほうが早い
MySQL,PostgreSQL,SQL Serverはdeleteのほうが早い

• MySQL

(8.0.26)

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

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

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
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);

select count(*) from tab1;
select * from tab1 order by rand() limit 20;

analyze table tab1;

--2. 動作確認

--2.1 deleteの場合

start transaction;
delete from tab1 where mod(col1,2) = 0;

2.59 sec

--2.2 truncate/insertの場合

start transaction;
insert into tab2 select * from tab1 where mod(col1,2) = 1;

5.78 sec

drop table tab1;
alter table tab2 rename to tab1;

--3. 結果

deleteのほうがinsertよりも早い

• Oracle

(19c)

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

drop table tab1 purge;
create table tab1(
    col1 int 
   ,col2 varchar2(1000)
   ,col3 varchar2(1000)
   ,col4 varchar2(1000)
   ,col5 varchar2(1000)
  );

drop table tab2 purge;
create table tab2(
    col1 int 
   ,col2 varchar2(1000)
   ,col3 varchar2(1000)
   ,col4 varchar2(1000)
   ,col5 varchar2(1000)
  );

declare
begin
for i in 1..1000000 loop
  insert into tab1 values(
    i
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
   );
  

end loop;
end;
/

commit;

select count(*) from tab1;
select * from tab1 order by dbms_random.value()  fetch first 20 rows only;

set time on
set timing on

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

--2. 動作確認

--2.1 deleteの場合

delete from tab1 where mod(col1,2) = 0;

経過: 00:00:13.45

--2.2 truncate/insertの場合

insert into tab2 select * from tab1 where mod(col1,2) = 1;

経過: 00:00:08.07

drop table tab1 purge;
alter table tab2 rename to tab1;

--3. 結果

insertのほうがdeleteよりも早い★

• PostgreSQL

(14)

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

drop table tab1;
create table tab1(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );
   
drop table tab2;
create table tab2(
    col1 bigint 
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );
   

start transaction;
insert into tab1 select
   g
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
  ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;

commit;

select count(*) from tab1;
select * from tab1 order by random() limit 20;

\timing 1

analyze tab1;

--2. 動作確認

--2.1 deleteの場合

start transaction;
delete from tab1 where mod(col1,2) = 0;

00:01.362

--2.2 truncate/insertの場合

start transaction;
insert into tab2 select * from tab1 where mod(col1,2) = 1;

00:02.396

drop table tab1;
alter table tab2 rename to tab1;

--3. 結果

deleteのほうがinsertよりも早い

• SQL Server

(2019)

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

drop table tab1;
create table tab1(
    col1 bigint not null
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

drop table tab2;
create table tab2(
    col1 bigint not null
   ,col2 varchar(1000)
   ,col3 varchar(1000)
   ,col4 varchar(1000)
   ,col5 varchar(1000)
   );

set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 100000
begin
  insert into tab1 values(
     @i
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
    );
 set @i = @i + 1;
end
commit;

select count(*) from tab1;
select top 20 * from tab1 order by newid();

set statistics time on

update statistics tab1;

--2. 動作確認

--2.1 deleteの場合

begin transaction;
delete from tab1 where col1 % 2 = 0;

経過時間 = 269 ミリ秒

--2.2 truncate/insertの場合

begin transaction;
insert into tab2 select * from tab1 where col1 % 2 = 1;

経過時間 = 546 ミリ秒

drop table tab1;
EXEC sp_rename 'dbo.tab2', 'tab1', 'OBJECT';

--3. 結果

deleteのほうがinsertよりも早い