delete taking loooong time

  • 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

  • davidsalazar01 - Wednesday, March 8, 2017 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

    There are rows in the archive table before you run this code.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • ChrisM@home - Wednesday, March 8, 2017 3:05 PM

    davidsalazar01 - Wednesday, March 8, 2017 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

    There 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.

  • 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

  • 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 with
    INSERT 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