March 8, 2017 at 2:51 pm
Hello All,
This block of code below is taking a long time to complete whereas other deletes similar to the logic below complete quickly. The tables used in the code are very small (ie. TransactionDetail (2300 rows) and Archive_TmpTransactionDetail (33 rows). Any ideas?
DECLARE @Error INT, @Count INT, @TmpCount INT
--SET NOCOUNT ON;
PRINT('')
PRINT('TransactionDetail')
CREATE TABLE #TD (TransactionDetailID INT)
CREATE CLUSTERED INDEX ix_td on #TD (TransactionDetailID)
ALTER TABLE TransactionDetail NOCHECK CONSTRAINT ALL
WHILE EXISTS(SELECT 1 FROM Archive_TmpTransactionDetail)
BEGIN
BEGIN TRAN
DELETE TOP(10000) u
OUTPUT DELETED.TransactionDetailID
INTO #TD
FROM dbo.TransactionDetail u
JOIN dbo.Archive_TmpTransactionDetail t
ON (t.TransactionDetailID = u.TransactionDetailID)
SELECT @TmpCount = COUNT(*) FROM #TD
DELETE u
FROM Archive_TmpTransactionDetail u
JOIN #TD e
ON (e.TransactionDetailID = u.TransactionDetailID)
SELECT @Error = @@Error
IF @Error <> 0
BEGIN
PRINT('There was an error: ' + CONVERT(VARCHAR, @Error))
ROLLBACK
END
ELSE
BEGIN
SELECT @Count = COUNT(*) FROM Archive_TmpTransactionDetail
PRINT('Committing changes to batch of ' + CONVERT(VARCHAR,@TmpCount) + '. ' + CONVERT(VARCHAR,@Count) + ' left.')
COMMIT
END
TRUNCATE TABLE #TD
END
PRINT ('Deleted [TransactionDetail] records.')
DROP TABLE #TD
ALTER TABLE TransactionDetail CHECK CONSTRAINT ALL
March 8, 2017 at 3:05 pm
davidsalazar01 - Wednesday, March 8, 2017 2:51 PMHello All,This block of code below is taking a long time to complete whereas other deletes similar to the logic below complete quickly. The tables used in the code are very small (ie. TransactionDetail (2300 rows) and Archive_TmpTransactionDetail (33 rows). Any ideas?
DECLARE @Error INT, @Count INT, @TmpCount INT
--SET NOCOUNT ON;
PRINT('')
PRINT('TransactionDetail')
CREATE TABLE #TD (TransactionDetailID INT)
CREATE CLUSTERED INDEX ix_td on #TD (TransactionDetailID)
ALTER TABLE TransactionDetail NOCHECK CONSTRAINT ALLWHILE EXISTS(SELECT 1 FROM Archive_TmpTransactionDetail)
BEGIN
BEGIN TRAN
DELETE TOP(10000) u
OUTPUT DELETED.TransactionDetailID
INTO #TD
FROM dbo.TransactionDetail u
JOIN dbo.Archive_TmpTransactionDetail t
ON (t.TransactionDetailID = u.TransactionDetailID)
SELECT @TmpCount = COUNT(*) FROM #TD
DELETE u
FROM Archive_TmpTransactionDetail u
JOIN #TD e
ON (e.TransactionDetailID = u.TransactionDetailID)
SELECT @Error = @@ErrorIF @Error <> 0
BEGIN
PRINT('There was an error: ' + CONVERT(VARCHAR, @Error))
ROLLBACK
END
ELSE
BEGIN
SELECT @Count = COUNT(*) FROM Archive_TmpTransactionDetail
PRINT('Committing changes to batch of ' + CONVERT(VARCHAR,@TmpCount) + '. ' + CONVERT(VARCHAR,@Count) + ' left.')
COMMIT
END
TRUNCATE TABLE #TD
END
PRINT ('Deleted [TransactionDetail] records.')
DROP TABLE #TD
ALTER TABLE TransactionDetail CHECK CONSTRAINT ALL
There are rows in the archive table before you run this code.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 8, 2017 at 3:23 pm
ChrisM@home - Wednesday, March 8, 2017 3:05 PMdavidsalazar01 - Wednesday, March 8, 2017 2:51 PMHello All,This block of code below is taking a long time to complete whereas other deletes similar to the logic below complete quickly. The tables used in the code are very small (ie. TransactionDetail (2300 rows) and Archive_TmpTransactionDetail (33 rows). Any ideas?
DECLARE @Error INT, @Count INT, @TmpCount INT
--SET NOCOUNT ON;
PRINT('')
PRINT('TransactionDetail')
CREATE TABLE #TD (TransactionDetailID INT)
CREATE CLUSTERED INDEX ix_td on #TD (TransactionDetailID)
ALTER TABLE TransactionDetail NOCHECK CONSTRAINT ALLWHILE EXISTS(SELECT 1 FROM Archive_TmpTransactionDetail)
BEGIN
BEGIN TRAN
DELETE TOP(10000) u
OUTPUT DELETED.TransactionDetailID
INTO #TD
FROM dbo.TransactionDetail u
JOIN dbo.Archive_TmpTransactionDetail t
ON (t.TransactionDetailID = u.TransactionDetailID)
SELECT @TmpCount = COUNT(*) FROM #TD
DELETE u
FROM Archive_TmpTransactionDetail u
JOIN #TD e
ON (e.TransactionDetailID = u.TransactionDetailID)
SELECT @Error = @@ErrorIF @Error <> 0
BEGIN
PRINT('There was an error: ' + CONVERT(VARCHAR, @Error))
ROLLBACK
END
ELSE
BEGIN
SELECT @Count = COUNT(*) FROM Archive_TmpTransactionDetail
PRINT('Committing changes to batch of ' + CONVERT(VARCHAR,@TmpCount) + '. ' + CONVERT(VARCHAR,@Count) + ' left.')
COMMIT
END
TRUNCATE TABLE #TD
END
PRINT ('Deleted [TransactionDetail] records.')
DROP TABLE #TD
ALTER TABLE TransactionDetail CHECK CONSTRAINT ALLThere are rows in the archive table before you run this code.
I agree with ChrisM@home, your archive table has rows in it before you run the code. You have your while loop:
WHILE EXISTS(SELECT 1 FROM Archive_TmpTransactionDetail)
and until that table is empty, it will never complete.
I think you likely want that loop to be based on the TransactionDetail table, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 9, 2017 at 7:46 am
You guys were right. I kicked it off last night to grab an execution plan and it ran overnight until this am in an infinite loop. There were 33 existing (orphaned) records in Archive_TmpTransactionDetail table. If I remove those records, I think the issue is solved. But this is an archive table and the procedure will continue to append records so the archive table can't be empty. What logic do you recommend I add to avoid this issue in the future? Thanks
March 9, 2017 at 8:09 am
If I understand the logic correctly, I think this is more what you want for your loop:WHILE EXISTS(SELECT 1
FROM TransactionDetail u
JOIN dbo.Archive_TmpTransactionDetail t
ON (t.TransactionDetailID = u.TransactionDetailID)
I think that shoudl capture the logic you are going for. Basically, I changed it so that as long at least 1 row would be deleted, then loop.
The alternate solution I can think of is to first move any orphaned items from Archive_TmpTransactionDetail into a different table and put them back when you are done. ie:SELECT *
INTO #tmpArchive_TmpTransactionDetail
FROM dbo.Archive_TmpTransactionDetail t
where t.TransactionDetailID not in (SELECT u.TransactionDetailID
FROM dbo.TransactionDetail u)
DELETE
FROM dbo.Archive_TmpTransactionDetail t
WHERE t TransactionDetailID in (SELECT tmp.TransactionDetailID
FROM #tmpArchive_TmpTransactionDetail
Followed by your code and then end it withINSERT INTO dbo.Archive_TmpTransactionDetail t
SELECT *
FROM #tmpArchive_TmpTransactionDetail
DROP TABLE #tmpArchive_TmpTransactionDetail
The first solution is a bit cleaner, but the second solution could be modified to store orphaned TransactionDetailID's in a different location and deal with them at a later date.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply