SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

TOP と ROWCOUNT を使用したデータ削除 その 1

leave a comment

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 秒

今まで、件数を絞って複数回削除したほうが早いのかとなんとなく思っていたのですがそれほど差は出ないみたいです。
# あまりにも細かい件数で削除すると処理時間が劣化するのはわかっていたのですが。

途中でトランザクションログを切り捨てるためにチェックポイントを発生させたい場合や、トランザクションの
セーブポイントを作りたい場合以外は件数を絞る必要はないかもしれないですね。

次は件数を絞って削除した際の比較をしてみたいと思います。

Written by masayuki.ozawa

11月 25th, 2009 at 3:41 pm

Posted in SQL Server

Leave a Reply

*