OUTPUTを使用して文からSQL Serverテーブルから大量のレコードを削除およびアーカイブする

OUTPUTを使用して文からSQL Serverテーブルから大量のレコードを削除およびアーカイブする
英文原文:
http://blog.extreme-advice.com/2013/01/08/delete-and-archive-bulk-record-from-sql-server-table-with-output-clause/
私のFacebookの友达と古い同僚、彼は財務製品のチームのリーダーで、私に何百万もの記録の時計を持っていて、この時計からいくつかの記録をアーカイブしたいと聞いて、彼はどうすればいいですか?
単純なDELETE文では、データベースが単純なリカバリ・モードでない場合に大量のログ・ファイルが生成され、テーブルが非常に長い間ロックされ、大量のリソースが消費され、パフォーマンスに影響を及ぼします.
通常、短いトランザクションを保持するのは非常に良いです.私はいつも大量のDELETE/UPDATEレコードが好きです.特に、大量のレコードが生産環境に記録されている場合、ダライ・ラマのトランザクション環境で大量のレコードを削除するのに長い時間がかかります.DELETE操作をキャンセルするには数時間か1日かかる可能性があります.すべてがロールバックされ、長い時間がかかります.1000行ごとに削除され、実行が停止すると、最大1000行だけがロールバックされ、長くはかかりません.
サンプル・データベースを作成して、実装方法を示します.

--Create sample database
CREATE DATABASE ExtremeAdvice
GO
USE ExtremeAdvice
GO
--create sample table along with 100,000 rows
IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN
DROP TABLE orders
END
GO
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
DATEADD(minute, ABS(a.OBJECT_ID % 50000 ), CAST('2012-02-01' AS DATETIME)),
ABS(a.OBJECT_ID % 10),
CAST(ABS(a.OBJECT_ID) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
IF OBJECT_ID('ordersArchive', 'U') IS NOT NULL BEGIN
DROP TABLE ordersArchive
END
GO
CREATE TABLE ordersArchive (OrderID INT, OrderDate DATETIME, Amount MONEY, Refno INT)
GO

次に、サンプル環境を用意して、提供した条件に基づいてレコードを削除するストレージ・プロシージャを作成します.

USE [ExtremeAdvice]
GO
CREATE PROCEDURE [dbo].[SPordersArchive]
AS
SET NOCOUNT ON
DECLARE @cnt INT, @rows INT
SELECT @cnt = 1
DECLARE @msg VARCHAR(1024)
DECLARE @dt DATETIME
--creating infinite loop which will break itself whenever all record gets deleted based on condition given
WHILE 1=1
BEGIN
SELECT
@dt = GETDATE(),
@rows = 0
--Deleting records in bunch
DELETE TOP (1000) o
OUTPUT
deleted.OrderID,
deleted.OrderDate,
deleted.Amount,
deleted.RefNo
INTO ordersArchive(
OrderID
,OrderDate
,Amount
,RefNo
)
FROM Orders AS o (NOLOCK)
WHERE o.OrderID <=50000
SELECT @rows = @@ROWCOUNT
SELECT @cnt = @cnt + 1
SELECT @msg = 'Lap : ' + CAST(@cnt AS VARCHAR) + ' ARCHIVED ' + cast(@rows AS VARCHAR) + ' rows in ' + cast(DATEDIFF(second, @dt, GETDATE()) as varchar) + ' seconds'
RAISERROR(@msg, 0, 1) WITH NOWAIT
IF @rows = 0 BREAK;
WAITFOR DELAY '00:00:00.100'
END

このストレージ・プロシージャを実行するには、時間がかかります.画面に次の情報が表示されます.