MySQL TIPS 5 ストアドプロシージャで指定した数のダミーデータを用意する2 (失敗編)

以前のエントリーで作ったinsert_generate_seriesを複数INSERT,複数カラムで生成データを使用可能にしてみる

DROP   PROCEDURE insert_generate_series;
delimiter //
CREATE PROCEDURE insert_generate_series(
   IN target    VARCHAR(255) , -- insert対象のテーブル、カラム名を指定
   IN value     VARCHAR(255) , -- valuesを記述 @numがgenerate_seriesで生成された値になる
   IN start_num INT ,          -- 開始値
   IN end_num   INT ,          -- 終了値
   IN step      INT            -- 増分
)
begin
  set @num:= start_num;
  set @sql:= concat('INSERT INTO ',target,' VALUES ');
  while @num <= end_num do
    set @sql:=concat(@sql,' ( ', replace(value,'@num',@num) , ' )');
    if @num <> end_num then
      set @sql:=concat(@sql,',');
    end if;
    set @num:=@num + step;
  end while;
  prepare stmt from @sql;
  execute stmt; 
end;
//
delimiter ;

call insert_generate_series('user(user_name)','concat(md5(@num),@num)',1,100000,1);

Query OK, 100000 rows affected (9 min 43.20 sec);

ちょ、おま、なんだこの遅さは....。生成されたinsert文を実行するのは1秒未満ですが、どうやらINSERT文を生成する際のconcatに時間が掛かっているようです。

• 冗長になってしまうがループ中のIF文を取り除いてみる

DROP   PROCEDURE insert_generate_series;
delimiter //
CREATE PROCEDURE insert_generate_series(
   IN target    VARCHAR(255) , -- insert対象のテーブル、カラム名を指定
   IN value     VARCHAR(255) , -- valuesを記述 @numがgenerate_seriesで生成された値になる
   IN start_num INT ,          -- 開始値
   IN end_num   INT ,          -- 終了値
   IN step      INT            -- 増分
)
begin
  set @num:= start_num;
  set @sql:= concat('INSERT INTO ',target,' VALUES ');
  while @num < end_num do
    set @sql:=concat(@sql,' ( ', replace(value,'@num',@num) , ' ),');
    set @num:=@num + step;
  end while;
  set @sql:=concat(@sql,' ( ', replace(value,'@num',@num) , ' )');
  prepare stmt from @sql;
  execute stmt; 
end;
//
delimiter ;

call insert_generate_series('user(user_name)','concat(md5(@num),@num)',1,100000,1);

Query OK, 100000 rows affected (4 min 25.48 sec)

なんと2倍！！、驚きの速度に。

DROP   PROCEDURE insert_generate_series;
delimiter //
CREATE PROCEDURE insert_generate_series(
   IN target    VARCHAR(255) , -- insert対象のテーブル、カラム名を指定
   IN value     VARCHAR(255) , -- valuesを記述 @numがgenerate_seriesで生成された値になる
   IN start_num INT ,          -- 開始値
   IN end_num   INT ,          -- 終了値
   IN step      INT            -- 増分
)
begin
  set @num:= start_num;
  set @sql:= concat('INSERT INTO ',target,' VALUES ');
  while @num < end_num do
    set @sql:=concat(@sql,' ( ', replace(value,'@num',@num) , ' ),');
    if @num%100=0 then
      select @num;
    end if;
    set @num:=@num + step;
  end while;
  set @sql:=concat(@sql,' ( ', replace(value,'@num',@num) , ' )');
  prepare stmt from @sql;
  execute stmt; 
end;
//
delimiter ;

call insert_generate_series('user(user_name)','concat(md5(@num),@num)',1,100000,1);

concat時のユーザ変数のバッファが大きくなるほどに遅くなっていくのがわかる。IF文の数が減ったから早くなったのではなくconcatの実行回数が減ったので早くなったらしい。