Faster Deletion from a production database

I need to delete several million transactions from a production database. I
have tried the SP below but it takes 4 1/2 hours to delete 5,000
transactions. Can someone please look over this and let me know if there is
someway to speed up this process? I can't truncate the table because I stil
l
need the most recent (6 months) worth of transactions. The remaining
transactions date back to 10/21/2003 and have been backed up and now need to
be deleted.
Thanks in advance,
Matt
CREATE PROC dbo.ArchiveProduction
(
@.CutOffDate DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete transactions from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
FileContentsID int, FileReceiveID int)
INSERT INTO @.tmp
SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
FileContentsID,
r.[ID] AS FileReceiveID
FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
f.FileReceiveID
LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
WHERE r.FileFindDate < @.CutOffDate
-- Acknowledgments
DELETE FROM dbo.Acknowledgments
WHERE TransactionID IN
(
Select TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
16, 1)
RETURN -1
END
-- OutboundTranSet
DELETE FROM dbo.OutboundTranSet
FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
16, 1)
RETURN -1
END
-- OutboundBatch
DELETE FROM dbo.OutboundBatch
FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
16, 1)
RETURN -1
END
-- OutboundFile
DELETE FROM dbo.OutboundFile
WHERE [ID] IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
1)
RETURN -1
END
-- PayorResponse
DELETE FROM dbo.PayorResponse
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
16, 1)
RETURN -1
END
-- ClaimInfo
DELETE FROM dbo.ClaimInfo
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
RETURN -1
END
-- Transactions
-- AttachmentImages
DELETE FROM dbo.AttachmentImages
FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
16, 1)
RETURN -1
END
-- AttachmentsToClaims
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.AttachmentsToClaims', 16, 1)
RETURN -1
END
-- ClaimsToAttachments
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.ClaimsToAttachments', 16, 1)
RETURN -1
END
-- Transactions
DELETE FROM dbo.Transactions
WHERE [ID] IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
1)
RETURN -1
END
-- Batches
-- Batches
DELETE FROM dbo.Batches
WHERE [ID] IN
(
SELECT BatchID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
RETURN -1
END
-- FileContents
DELETE FROM dbo.FileContents
WHERE [ID] IN
(
SELECT FileContentsID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
1)
RETURN -1
END
-- FileReceive
DELETE FROM dbo.FileReceive
WHERE [ID] IN
(
SELECT FileReceiveID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
GO1) Don't use table variables for large amounts of data (more than say 500
rows), use temporary tables.
2) Use joins or EXISTS, not IN.
3) Create indexes on all columns that you join on, including those in the
temporary tables.
4) By the looks of it you can cut out quite a few joins, specially between
the OutboundFile and Transactions tables.
Jacco Schalkwijk
SQL Server MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>I need to delete several million transactions from a production database.
>I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
> is
> someway to speed up this process? I can't truncate the table because I
> still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
> to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
> 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks, Jacco! I'll give it a shot.
"Jacco Schalkwijk" wrote:

> 1) Don't use table variables for large amounts of data (more than say 500
> rows), use temporary tables.
> 2) Use joins or EXISTS, not IN.
> 3) Create indexes on all columns that you join on, including those in the
> temporary tables.
> 4) By the looks of it you can cut out quite a few joins, specially between
> the OutboundFile and Transactions tables.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>
>|||Depending on how many records are in the tmp Table, Your problem *might* be
because there are no indices on that tmp table. WHich means that each delet
e
must do a complete table scan on that table. SO, If there are a chunk of
records in there, I might suggest using four table variables instead, as
follows:
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchesIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
And then use these tables in all the delete queries instead... This will
allow each delete query to use Indexes on Table variables and may improve
performace substantially..
Also I suggest you put the errhandling at the end, and just "Goto" a named
label... It'll make the SP much cleaner and easier to maintain, as so:
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
/*SELECT t.[ID] as TranID,
b.[ID] BatchID,
t.OutboundFileID,
f.[ID] FileContentsID,
r.[ID] FileReceiveID
FROM FileReceive r
LEFT JOIN FileContents f ON f.FileReceiveID = r.[ID]
LEFT JOIN Batches b ON b.FileContentsID = f.[ID]
LEFT JOIN Transactions t ON t.BatchID = b.[ID]
WHERE r.FileFindDate < @.CutOffDate */
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0) -- This is the end of normal processing
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||oh oh, some extra stuff in there I neglected to delete.. Here's correctted
version...
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0)
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||What is the recovery model on your database? Try setting to simple recovery.
Also, create a loop where you delete only 100,000 records in each iteration
followed by a checkpoint. You will also want to perform a backup just prior
to this.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I
still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks to all of you for your help. With your input the delete now only
takes 2 1/2 minutes to delete 5,000 transactions and all associated items.
"Matt" wrote:

> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I st
ill
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 1
6,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16,
1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages
',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 1
6,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 1
6,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16
, 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>