interprism's blog

インタープリズム株式会社の開発者ブログです。

TABLOCKでデータ移行を高速化してみた

この投稿は インタープリズムの面々が、普段の業務に役立つ記事を丹精込めて書き上げる! Advent Calendar 2016 - Qiitaの16日目 の記事です。

ssといいます。

1月から新しい現場で働くことになったので、 前の現場でやったことの一つを備忘録として書きたいと思います。

データ移行の差分SQL高速化

開発ツールがバージョンアップしたときにお客様のデータを移行する差分SQLを作成していました。 移行は下記手順の通りでした。 (データベースは SQL Serverです)

  1. バージョンアップ後のテーブルを作成し、tmpテーブルとする
  2. tmpテーブルにデータ移行
  3. 移行前のテーブルを削除し、tmpテーブルの名前を修正する

今までは問題なくお客様のデータを移行できていましたが、 お客様のデータ量が多いとデータ移行に時間がかかっていました。 そのため、差分SQLの実行時間を短縮できないかと依頼されました。

修正内容

Use TABLOCK to boost your INSERT INTO … SELECT performance | drillchina

上記サイトを参考にして、 tmpテーブルのINSERT文でtmpテーブル名の後に「WITH (TABLOCK)」を追加しました。

修正前:

INSERT INTO [tmpテーブル] …

修正後:

INSERT INTO [tmpテーブル] WITH (TABLOCK) …

修正結果

実際のデータを使用し、どのくらい時間がかかったか計測しました。 今回の差分SQLで移行するデータ量は

テーブル名 レコード数
テーブルA 約300万件
テーブルB 約2500万件
合計 約2800万件

です。 かかった時間は以下の通り。

修正前の実行時間 修正後の実行時間
約30分 約6分 5倍の高速化!

まとめ

TABLOCKを使用するには以下の制約があります。

  1. The database must be in bulk logged recovery model.
  2. The destination table must be empty or without clustered index.
  3. There is no non-clustered index on the destination table.

しかし、制約を満たす場合は大幅な速度の改善が見込めるので、 積極的に使用してみてはいかがでしょうか。

インタープリズムの面々が、普段の業務に役立つ記事を丹精込めて書き上げる! Advent Calendar 2016 - Qiita17日目の記事

PAGE TOP