SQL Serverで大量のデータを更新/削除するクエリの例

背景

大量のデータの更新や削除をする場合、1つのクエリで処理するとパフォーマンスやロックの問題が発生する可能性があります。
明示的にトランザクションを貼っていなくても、１つ１つのクエリがトランザクションとして実行されるため、トランザクションログが肥大化してしまいます。

-- 件数が多過ぎる場合、実行完了までトランザクションログが肥大化し続ける
DELETE FROM Logs WHERE LogDate < DATEADD(month, -6, GETDATE());

これによってデータベース全体のパフォーマンスが低下したり、ディスク容量が不足したりする可能性があります。
また、実行中は該当の行やページにロックがかかるため、他のクエリがブロックされることがありますし、SQL Serverの場合はロックエスカレーションという機能によりテーブル全体にロックがかかることがあります。
回避するためには、1つ1つのトランザクションで処理する件数が少なくなるようにバッチ処理を行うことが有効です。

クエリ例

次のように、WHILE文で回し、分割して実行することで1つのトランザクションを短く、ロックの競合を最小限に抑えることができます。

更新クエリの例

-- トランザクションが開かれていないことのチェック
IF @@TRANCOUNT > 0
BEGIN
	RAISERROR ('Transaction is opend.', 16, 1);
END

DECLARE @BatchSize INT = 1000;


-- @@ROWCOUNTを0より大きい値にしてWHILE文の条件を満たすようにするためのSELECT文
SELECT 1;
-- 何も返したくないならば declare @dummy int = 0; などでも@@ROWCOUNTは1になる

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP (@BatchSize) Orders
    SET Status = 'Processed'
    WHERE Status = 'Pending';
END

削除クエリの例

IF @@TRANCOUNT > 0
BEGIN
	RAISERROR ('Transaction is opend.', 16, 1);
END

DECLARE @BatchSize INT = 1000;
SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (@BatchSize) FROM Logs
    WHERE LogDate < DATEADD(month, -6, GETDATE());
END

WHERE句の条件によっては、インデックスの効率的な利用ができない場合があるので、実行計画を確認し、状況によっては適切なインデックスを作成することも重要です。

WHILE文を回す前に軽く動作確認したい場合

上記クエリを実行すればデータの更新/削除が実行され続けますが、更新/削除されたものはバックアップファイルから復元しない限り戻せません。
WHILE文を回し始める前に本当にクエリがあっているか事前に確認したい場合や、1回分だけ実行して結果を確認したい場合などには、次のようにROLLBACK やCOMMITで@@ROWCOUNT が0になることを利用した方法が可能です。

一旦 rollback したい場合

IF @@TRANCOUNT > 0
BEGIN
	RAISERROR ('Transaction is opend.', 16, 1);
END
DECLARE @BatchSize INT = 1000;
SELECT 1;

SELECT COUNT(*) FROM Logs WHERE LogDate < DATEADD(month, -6, GETDATE()) -- 削除したいログの数

WHILE @@ROWCOUNT > 0
BEGIN
    BEGIN TRAN
    
    DELETE TOP (@BatchSize) FROM Logs
    WHERE LogDate < DATEADD(month, -6, GETDATE());

    SELECT COUNT(*) FROM Logs WHERE LogDate < DATEADD(month, -6, GETDATE()) -- @BatchSizeの数だけ減っていることが確認できる
    ROLLBACK -- このコマンドが実行されることで@@ROWCOUNT が 0になるためWHILE文から抜ける
END

SELECT COUNT(*) FROM Logs WHERE LogDate < DATEADD(month, -6, GETDATE()) -- ロールバックされることで件数が元に戻る

一回 commit したい場合

IF @@TRANCOUNT > 0
BEGIN
	RAISERROR ('Transaction is opend.', 16, 1);
END
DECLARE @BatchSize INT = 1000;
SELECT 1;

SELECT COUNT(*) FROM Logs WHERE LogDate < DATEADD(month, -6, GETDATE()) -- 削除したいログの数

WHILE @@ROWCOUNT > 0
BEGIN
    BEGIN TRAN
    
    DELETE TOP (@BatchSize) FROM Logs
    WHERE LogDate < DATEADD(month, -6, GETDATE());

    SELECT COUNT(*) FROM Logs WHERE LogDate < DATEADD(month, -6, GETDATE())
    COMMIT -- このコマンドが実行されることで@@ROWCOUNT が 0になるためWHILE文から抜ける
END

SELECT COUNT(*) FROM Logs WHERE LogDate < DATEADD(month, -6, GETDATE()) -- @BatchSizeの数だけ減っていることが確認できる