Delete文を利用して大量のレコードを削除すると実行に非常に長い時間がかかる - SQL Server

Delete文を利用して大量のレコードを削除すると実行に非常に長い時間がかかる現象について紹介します。

概要

非常にレコード数の多いテーブルで、下記のような大量のレコードを削除するSQLを実行すると処理に非常に長い時間がかかります。

delete * from BigTable

また、トランザクションログのサイズが急激に増え、ディスクフルなどの現象が発生することもあります。

原因

1つのトランザクションでレコードを1つずつ削除するため、インデックスの更新やトランザクションのサイズが大きくなってしまうことが原因と考えられます。

対処法

以下の対処法があります。

topを利用して一度に削除するレコード数を小さくする

topを指定して一度に削除するレコード数を減らし、複数回に分割すると実行時間を短くできます。

delete top(10000) from BigTable

検索条件を利用して一度に削除するレコード数を小さくする

検索条件を利用して削除したいレコードを絞り込めるのであれば、条件を追加します。

例

delete * from BigTable where (date >= '2019-1-1' and date < '2020-1-1')

上記のSQLを下記のSQLに分割します。

delete * from BigTable where (date >= '2019-1-1' and date < '2019-2-1')
delete * from BigTable where (date >= '2019-2-1' and date < '2019-3-1')
delete * from BigTable where (date >= '2019-3-1' and date < '2019-4-1')
delete * from BigTable where (date >= '2019-4-1' and date < '2019-5-1')
delete * from BigTable where (date >= '2019-5-1' and date < '2019-6-1')
delete * from BigTable where (date >= '2019-6-1' and date < '2019-7-1')
delete * from BigTable where (date >= '2019-7-1' and date < '2019-8-1')
delete * from BigTable where (date >= '2019-8-1' and date < '2019-9-1')
delete * from BigTable where (date >= '2019-9-1' and date < '2019-10-1')
delete * from BigTable where (date >= '2019-10-1' and date < '2019-11-1')
delete * from BigTable where (date >= '2019-11-1' and date < '2019-12-1')
delete * from BigTable where (date >= '2019-12-1' and date < '2020-1-1')

非クラスタ化インデックスを削除する

レコードの削除時にインデックスの更新が実行されますが、インデックスが多い場合、削除時のインデックス更新処理に処理時間がかかる場合があります。 非クラスタ化インデックスが複数ある場合、非クラスタ化インデックスを削除すると削除速度が改善する場合があります。
削除条件に非クラスタ化インデックスのフィールドを条件に含めてしまうと 検索処理に時間がかかるため、クラスタ化インデックスのフィールドの値で絞り込む必要があります。

TRUNCATE TABLE 文を利用する

すべてのレコードを削除する場合は、TRUNCATE TABLE の利用を考えます。TRUNCATE TABLE 文についてはこちらの記事を参照してください。