エクスポートインポート方法の速度比較

• MySQL

(8.0.27)

方法1 : mysqldump => mysql(バルクインサート)
方法2 : csv出力 => load data infile文

結果:
方法1 : 18秒+5分53秒=6分11秒
方法2 : 16秒+5分18秒=5分34秒

バルクインサートとload data で大差なし

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

drop table tab1;
create table tab1(
    col1 int primary key
   ,col2 bigint
   ,col3 datetime
   ,col4 varchar(100)
   ,col5 char(100)
   );

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() * 1000000000000)+1
    ,date_add('2001-01-01', interval floor(365*20*24*3600 * rand()) second)
    ,substring(md5(rand()), 1, 30)
    ,substring(md5(rand()), 1, 30)
    );
  end while;
  commit;
end
//
delimiter ;

call proc1(10000000);
select count(*) from tab1;

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

create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);

analyze table tab1;
select
   table_name
  ,table_rows
  ,avg_row_length
  ,data_length/1024/1024/1024 tableGB
  ,index_length/1024/1024/1024 indexGB
from information_schema.tables
where table_schema = database()
and table_name = 'tab1'
;

件数: 1千万
データサイズ: 1.7G

-- 2. 方法1 : mysqldump => mysql

-- 2.1 mysqldump

time mysqldump -uroot -p -t test tab1 > a.sql

real    0m18.850s
user    0m10.128s
sys     0m1.097s

出力ファイルサイズ: 1.1G

-- 2.2 mysql

truncate table tab1;

time mysql test < a.sql

real    5m53.096s
user    0m9.660s
sys     0m0.643s

-- 3. 方法2 : csv出力 => load data infile文

-- 3.1 csv出力

time mysql test <<EOF
select * into outfile '/var/lib/mysql-files/a.csv'
fields terminated by ',' optionally enclosed by '"'
from tab1;
EOF

real    0m16.537s
user    0m0.005s
sys     0m0.008s

csvファイルサイズ: 1.1G

-- 3.2 load data infile文

truncate table tab1;

time mysql test <<EOF
load data infile '/var/lib/mysql-files/a.csv'
into table tab1
fields terminated by ',' optionally enclosed by '"'
;
EOF

real    5m18.560s
user    0m0.004s
sys     0m0.012s

• Oracle

(19c)

方法1 : expdp => impdp
方法2 : CSV出力 => SQL*Loader

結果:
方法1 : 43秒+3分27秒=4分10秒
方法2 : 59秒+1分52秒=2分51秒

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

drop table tab1 purge;
create table tab1(
    col1 int primary key
   ,col2 int
   ,col3 timestamp
   ,col4 varchar2(100)
   ,col5 char(100)
   );

declare
begin
for i in 1..10000000 loop
  insert into tab1 values
  (i
  ,floor(dbms_random.value(1, 1000000000001) )
  ,to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 365*20*24*3600))/24/3600
  ,substr(standard_hash(dbms_random.value(), 'MD5'),1,30)
  ,substr(standard_hash(dbms_random.value(), 'MD5'),1,30)
  );
end loop;
end;
/

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

create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);

select sum(bytes/1024/1024/1024) bytes from user_segments where segment_name ='TAB1';

件数: 1千万
データサイズ: 1.75G

-- 2. 方法1: expdp => impdp

-- 2.1 expdp

time expdp test/test@pdb1 directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.log tables=TAB1 reuse_dumpfiles=yes

real    0m43.313s
user    0m0.017s
sys     0m0.026s

ダンプサイズ: 1.5G

-- 2.2 impdp

truncate table tab1;

time impdp test/test@pdb1 directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.log tables=TAB1 content=data_only

real    3m27.429s
user    0m0.020s
sys     0m0.054s

-- 3. 方法2: CSV出力 => SQL*Loader

-- 3.1 CSV出力

vi a.sql

set trims on
set head off
set feed off
set echo off
set termout off
set verify off
set pagesize 0
set linesize 32767
set markup csv on quote on
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
spool a.csv
select * from tab1;
exit
spoo off

time sqlplus test/test@pdb1 @a.sql

wc -l a.csv

real    0m59.319s
user    0m38.244s
sys     0m4.048s

csvファイルサイズ: 1.7G

-- 3.2 SQL*Loader

vim a.ctl

load data
infile 'a.csv'  "str '\n'"
into table tab1
insert
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
( col1 
 ,col2 
 ,col3 timestamp "YYYY/MM/DD HH24:MI:SS"
 ,col4 char(100)
 ,col5 char(100)
)

truncate table tab1;

time sqlldr test/test@pdb1 control=a.ctl data=a.csv log=a.log direct=true

real    1m52.530s
user    0m24.097s
sys     0m3.689s

• PostgreSQL

(14)

方法1 : pg_dump(プレーンテキスト) => psql
方法2 : pg_dump(カスタムアーカイブ) => pg_restore
方法3 : \COPY TO => \COPY FROM

結果:
方法1 : 14秒+18分52秒=19分6秒
方法2 : 1分3秒+18分27秒=19分30秒
方法3 : 12秒+17分56秒=18分8秒

pg_dumpはプレーンテキストもカスタムアーカイブも同じくらいの時間

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

drop table tab1;
create table tab1(
    col1 int primary key
   ,col2 bigint
   ,col3 timestamp
   ,col4 varchar(100)
   ,col5 char(100)
   );

start transaction;
insert into tab1 select
   g
  ,floor(random() * 1000000000000)+1
  ,'2001-01-01'::date + CAST( floor(365*20*24*3600*random()) || 'second' AS interval)
  ,substring(md5(random()::text), 1, 30)
  ,substring(md5(random()::text), 1, 30)
from generate_series(1,10000000) g;

commit;

select count(*) from tab1;

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

create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);

SELECT pg_size_pretty(pg_relation_size('tab1'));

件数: 1千万
データサイズ: 1.77G

-- 2. 方法1: pg_dump(プレーンテキスト) => psql

-- 2.1 pg_dump(プレーンテキスト)

time pg_dump -Fp -a -t tab1 test > a.sql

real    0m14.690s
user    0m0.700s
sys     0m1.160s

出力ファイルサイズ: 1.7G

-- 2.2 psql(COPY文)

truncate table tab1;

time psql test < a.sql

real    18m52.547s
user    0m1.517s
sys     0m1.209s

-- 3. 方法2: pg_dump(カスタムアーカイブ) => pg_restore

-- 3.1 pg_dump(カスタムアーカイブ)

time pg_dump -Fc -a -t tab1 test > a.dmp

real    1m3.621s
user    0m51.563s
sys     0m1.143s

出力ファイルサイズ: 0.55G

-- 3.2 pg_restore

truncate table tab1;

time pg_restore -d test a.dmp

real    18m27.133s
user    0m8.240s
sys     0m0.952s

-- 4. 方法3: \COPY TO => \COPY FROM
-- 4.1 \COPY TO

time psql test -c "\copy tab1 to '/tmp/tab1.csv' (format csv);"

real    0m12.169s
user    0m0.634s
sys     0m1.221s

csvファイルサイズ: 1.7G

-- 4.2 \COPY FROM

truncate table tab1;

time psql test -c "\copy tab1 from '/tmp/tab1.csv' (format csv);"

real    17m56.002s
user    0m1.529s
sys     0m1.210s

• SQL Server

(2019)

方法1 : bcp out => bcp in
方法2 : bcp out => bulk insert文

結果:
方法1 : 32秒+4分35秒=5分7秒
方法2 : 32秒+3分27秒=3分59秒

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

drop table tab1;
create table tab1(
    col1 int primary key
   ,col2 bigint
   ,col3 datetime2
   ,col4 varchar(100)
   ,col5 char(100)
   );

set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 10000000
begin
  insert into tab1 values(
     @i
    ,floor(rand() * 1000000000000)+1
    ,dateadd(second,floor(365*20*24*3600 * rand()),'2001-01-01')
    ,substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30)
    ,substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30)
    );
  set @i = @i + 1;
end
commit;
select count(*) from tab1;

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

create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);

exec sp_spaceused 'dbo.tab1';
go

件数: 1千万
データサイズ: 1.56G

-- 2. 方法1 : bcp out => bcp in

-- 2.1 bcp out

bcp test.dbo.tab1 out "C:\a.csv"  -T -c -t,

10000000 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 32672  平均 : (306072.47 行/秒)

csvファイルサイズ: 1.69G

-- 2.2 bcp in

truncate table tab1;

bcp test.dbo.tab1 in "C:\a.csv"  -T -c -t,

10000000 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 275453 平均 : (36303.84 行/秒)

-- 3. 方法2 : bcp out => bulk insert文

-- 3.1 bcp out

bcp test.dbo.tab1 out "C:\a.csv"  -T -c -t,

10000000 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 32672  平均 : (306072.47 行/秒)

csvファイルサイズ: 1.69G

-- 3.2 bulk insert文

truncate table tab1;

bulk insert tab1 from 'C:\a.csv'  with ( fieldterminator = ',' )

3:27