この投稿は インタープリズムの面々が、普段の業務に役立つ記事を丹精込めて書き上げる! Advent Calendar 2016 - Qiitaの16日目 の記事です。
ssといいます。
1月から新しい現場で働くことになったので、 前の現場でやったことの一つを備忘録として書きたいと思います。
データ移行の差分SQL高速化
開発ツールがバージョンアップしたときにお客様のデータを移行する差分SQLを作成していました。 移行は下記手順の通りでした。 (データベースは SQL Serverです)
- バージョンアップ後のテーブルを作成し、tmpテーブルとする
- tmpテーブルにデータ移行
- 移行前のテーブルを削除し、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を使用するには以下の制約があります。
- The database must be in bulk logged recovery model.
- The destination table must be empty or without clustered index.
- There is no non-clustered index on the destination table.
しかし、制約を満たす場合は大幅な速度の改善が見込めるので、 積極的に使用してみてはいかがでしょうか。
インタープリズムの面々が、普段の業務に役立つ記事を丹精込めて書き上げる! Advent Calendar 2016 - Qiitaの17日目の記事