SQL Server 2005 以降では DELETE TOP と ROWCOUNT を使用してループの中で指定した件数ずつ
削除することができるようになっています。
# 2000 では SET ROWCOUNT で実施できますね。
一度の DELETE で一括削除した場合と TOP で指定した件数ずつ削除していった場合でどれくらい
処理時間に差がでるか気になったので試してみました。
まずは全件削除をした場合から。
# TRUNCATE でページのビットマップを解除するのが一番早いですが。
今回使用するテーブルのスクリプトは以下になります。
– テストテーブルのスクリプト –
|
USE [WORK]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl1](
??? [Col1] [uniqueidentifier] NOT NULL,
??? [Col2] [nchar](4000) NULL,
??? [Col3] [date] NULL,
CONSTRAINT [PK_Tbl1] PRIMARY KEY CLUSTERED
(
??? [Col1] ASC
)WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Tbl1] ON [dbo].[Tbl1]
(
??? [Col3] ASC
)WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
GO
|
?
今までは GUID と nchar の単純なテーブルだったのですが、今回は date を追加して、非クラスタ化インデックスを
設定しています。
こちらのテーブルデータを挿入します。
# 復旧モデルを単純にしてログを自動切り捨てにし、断片化を解消しています。
– データの挿入 –
|
SET NOCOUNT ON
USE [WORK]
ALTER DATABASE [WORK] SET RECOVERY SIMPLE
TRUNCATE TABLE [dbo].[Tbl1]
DECLARE @i INT = 0
DECLARE @date date = ‘2000-01-01’
WHILE (@i < 50000)
BEGIN
??? INSERT INTO [dbo].[Tbl1] VALUES (NEWID(), NCHAR(@i), DATEADD(day, @i, @date))
??? SET @I += 1
END
ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REBUILD
ALTER INDEX [IX_Tbl1] ON [dbo].[Tbl1] REBUILD
|
?
各削除の処理を実行する前に、チェックポイントとキャッシュのクリアしてから処理時間を計測しています。
– チェックポイントとキャッシュのクリア –
|
CHECKPOINT
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
|
?
この状態でデータを一括削除してみます。
– データの一括削除 –
|
SET NOCOUNT ON
USE WORK
DELETE FROM [dbo].[Tbl1]
|
?
実行にかかった時間が以下になります。
処理時間は3 回計測しています。
– データの一括削除の処理時間 –
| 1 回目 |
16 秒 |
| 2 回目 |
15 秒 |
| 3 回目 |
13 秒 |
?
次は TOP と ROWCOUNT を使用して 1,000 件ずつ削除してみます。
– 1,000 件ずつ削除 –
|
SET NOCOUNT ON
USE WORK
WHILE(0=0)
BEGIN
??? DELETE TOP(1000) FROM [dbo].[Tbl1]
??? IF @@ROWCOUNT = 0
??? BEGIN
??????? BREAK
??? END
END
|
?
– 1,000 件ずつ削除した場合の処理時間 –
| 1 回目 |
1 分 41 秒 |
| 2 回目 |
1 分 42 秒 |
| 3 回目 |
1 分 39 秒 |
?
1,000 件ずつ削除するとかなり処理が遅くなっていますね。
削除件数を増やして試してみました。
– 10,000 件ずつ削除した場合の処理時間 –
| 1 回目 |
13 秒 |
| 2 回目 |
13 秒 |
| 3 回目 |
21 秒 |
– 20,000 件ずつ削除した場合の処理時間 –
| 1 回目 |
9 秒 |
| 2 回目 |
14 秒 |
| 3 回目 |
15 秒 |
– 30,000 件ずつ削除した場合の処理時間 –
| 1 回目 |
13 秒 |
| 2 回目 |
17 秒 |
| 3 回目 |
15 秒 |
– 40,000 件ずつ削除した場合の処理時間 –
| 1 回目 |
7 秒 |
| 2 回目 |
13 秒 |
| 3 回目 |
16 秒 |
今まで、件数を絞って複数回削除したほうが早いのかとなんとなく思っていたのですがそれほど差は出ないみたいです。
# あまりにも細かい件数で削除すると処理時間が劣化するのはわかっていたのですが。
途中でトランザクションログを切り捨てるためにチェックポイントを発生させたい場合や、トランザクションの
セーブポイントを作りたい場合以外は件数を絞る必要はないかもしれないですね。
次は件数を絞って削除した際の比較をしてみたいと思います。
