インポート時のインデックス影響

DB

(8.0.27)


-- 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(1000000);
select count(*) from tab1;
select * from tab1 order by rand() limit 20;

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


-- 2. インデックス追加
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
create index ind15 on tab1(col5);

-- 3. インポート時間計測
truncate table tab1;

time mysql test < a.sql


インデックスなしの場合
real    0m10.885s

インデックスありの場合
real    0m33.380s

-- 4. インデックス作成時間計測
mysql> create index ind12 on tab1(col2);
Query OK, 0 rows affected (1.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind13 on tab1(col3);
Query OK, 0 rows affected (1.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind14 on tab1(col4);
Query OK, 0 rows affected (4.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ind15 on tab1(col5);
Query OK, 0 rows affected (8.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

インポート時間を削減したい場合は、インデックス削除→インポート→インデックス作成で、改善できると思われる

 

(19c)


-- 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..1000000 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;

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

-- 2. インデックス追加
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
create index ind15 on tab1(col5);

-- 3. インポート時間計測
truncate table tab1;

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

インデックスなしの場合
real    0m19.154s

インデックスありの場合
real    0m39.177s

-- 4. インデックス作成時間計測
pdb1(TEST)> create index ind12 on tab1(col2);
索引が作成されました。
経過: 00:00:00.69
pdb1(TEST)> create index ind13 on tab1(col3);
索引が作成されました。
経過: 00:00:00.77
pdb1(TEST)> create index ind14 on tab1(col4);
索引が作成されました。
経過: 00:00:01.11
pdb1(TEST)> create index ind15 on tab1(col5);
索引が作成されました。
経過: 00:00:02.06

インポート時間を削減したい場合は、インデックス削除→インポート→インデックス作成で、改善できると思われる

(14)


-- 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,1000000) g;

commit;

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


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

-- 2. インデックス追加
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
create index ind15 on tab1(col5);

-- 3. インポート時間計測
truncate table tab1;

time psql test < a.sql

インデックスなしの場合
real    0m4.164s

インデックスありの場合
real    0m26.311s

-- 4. インデックス作成時間計測

test=# create index ind12 on tab1(col2);
CREATE INDEX
時間: 900.544 ミリ秒
test=# create index ind13 on tab1(col3);
CREATE INDEX
時間: 791.061 ミリ秒
test=# create index ind14 on tab1(col4);
CREATE INDEX
時間: 1248.619 ミリ秒(00:01.249)
test=# create index ind15 on tab1(col5);
CREATE INDEX
時間: 2322.612 ミリ秒(00:02.323)

インポート時間を削減したい場合は、インデックス削除→インポート→インデックス作成で、改善できると思われる

 

(2019)

 

-- 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 <= 1000000
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();

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


-- 2. インデックス追加
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
create index ind15 on tab1(col5);

-- 3. インポート時間計測
truncate table tab1;

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

インデックスなしの場合
クロック タイム (ミリ秒) 合計     : 13110  平均 : (76277.65 行/秒)

インデックスありの場合
クロック タイム (ミリ秒) 合計     : 107781 平均 : (9278.07 行/秒)

-- 4. インデックス作成時間計測

create index ind12 on tab1(col2);
、CPU 時間 = 1625 ミリ秒、経過時間 = 2071 ミリ秒。

create index ind13 on tab1(col3);
、CPU 時間 = 1547 ミリ秒、経過時間 = 1465 ミリ秒。

create index ind14 on tab1(col4);
、CPU 時間 = 4703 ミリ秒、経過時間 = 4025 ミリ秒。

create index ind15 on tab1(col5);
、CPU 時間 = 6953 ミリ秒、経過時間 = 6740 ミリ秒。


インポート時間を削減したい場合は、インデックス削除→インポート→インデックス作成で、改善できると思われる