SQL Server : cleaning old data takes too long

Question

I'm trying to clean old data (around 5000 entries in each table) from my SQL Server database, but it takes too long (more than an hour) as I'm looping a CURSOR inside another one.

BEGIN
    DECLARE @UserId int
    DECLARE @productNum varchar(50)

    DECLARE user_ids CURSOR FOR SELECT id
    FROM Users
    WHERE productId IN (SELECT ap.id 
                        FROM Account AS a, AccountProduct AS ap 
                        WHERE a.id = ap.accountId 
                          AND a.name IN ('XXXXXX', 'XXXXXX', 'XXXXXX', 'XXXXXX'))
    
    DECLARE product_cur CURSOR FOR 
        SELECT ap.id 
        FROM Account AS a, AccountProduct AS ap
        WHERE a.id = ap.accountId 
          AND a.name IN ('XXXXXX', 'XXXXXX', 'XXXXXX', 'XXXXXX') 
    
    OPEN user_ids

    FETCH NEXT FROM user_ids INTO @UserId

    WHILE @@FETCH_STATUS = 0
    BEGIN
        OPEN product_cur 

        FETCH NEXT FROM product_cur INTO @productNum

        WHILE @@FETCH_STATUS = 0
        BEGIN
            DELETE FROM UserRole 
            WHERE userId = @UserId 
              AND productId = (SELECT id 
                               FROM AccountProduct 
                               WHERE number = @productNum)
                
            DELETE FROM AccountProduct 
            WHERE number = @productNum

            FETCH NEXT FROM product_cur INTO @productNum
        END
        CLOSE product_cur 

        DELETE FROM Users 
        WHERE id = @UserId 
          AND accountId IN (SELECT id FROM Account 
                            WHERE name IN ('XXXXXX', 'XXXXXX', 'XXXXXX', 'XXXXXX'))

        FETCH NEXT FROM user_ids INTO @UserId
    END

    CLOSE user_ids
    DEALLOCATE user_ids
    DEALLOCATE product_cur 
END

Do you know a better way to do that task?

Answer 1

You can go for frequent committing with deletion. I would suggest to delete one userid at a time in a transaction and commit them.

Go for batch based deletion.

DECLARE @UserIdsToDelete TABLE(RowNo int, UserId int)
DECLARE @ProductsToDelete TABLE(RowNo int, ProductId int)

INSERT INTO @UserIdsToDelete
SELECT ROW_NUMBER() OVER (ORDER BY UserId) as RowNo, UserId
    FROM Users
    WHERE productId IN (SELECT ap.id 
                        FROM Account AS a, AccountProduct AS ap 
                        WHERE a.id = ap.accountId 
                          AND a.name IN ('XXXXXX', 'XXXXXX', 'XXXXXX', 'XXXXXX'))

INSERT INTO @productsToDelete
SELECT ROW_NUMBER() OVER (ORDER BY ap.id) as RowNo, ap.id 
        FROM Account AS a, AccountProduct AS ap
        WHERE a.id = ap.accountId 
          AND a.name IN ('XXXXXX', 'XXXXXX', 'XXXXXX', 'XXXXXX') 


DECLARE @UserIdForDeletion INT
DECLARE @RowNoForDeletion INT = 1

SET @UserIdForDeletion = (SELECT UserID from 
@UserIdsToDelete 
WHERE RowNO = @RowNoForDeletion )
 
-- Deletion of Users
WHILE (@UserIdForDeletion IS NOT NULL )
  BEGIN

BEGIN TRY
        SET XACT_ABORT ON

        BEGIN TRANSACTION

   DELETE FROM UserRole
   WHERE UserId = @UserIdForDeletion 
   AND productId IN (SELECT ProductID from @ProductsToDelete)

    DELETE FROM Users 
        WHERE id = @UserId 
          AND accountId IN (SELECT id FROM Account 
                            WHERE name IN ('XXXXXX', 'XXXXXX', 'XXXXXX', 'XXXXXX'))

    COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH

        IF XACT_STATE() <> 0 
            ROLLBACK TRANSACTION;
    
        THROW;
    END CATCH

@RowNoForDeletion += 1;
SET @UserIdForDeletion = (SELECT UserID from 
@UserIdsToDelete 
WHERE RowNO = @RowNoForDeletion )
END

-- Delete the account products
BEGIN TRY
        SET XACT_ABORT ON

        BEGIN TRANSACTION
DELETE FROM AccountProduct 
            WHERE number IN (SELECT ProductID from @ProductsToDelete)
COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH

        IF XACT_STATE() <> 0 
            ROLLBACK TRANSACTION;
    
        THROW;
    END CATCH

Answer 2

Don't use cursor as it would definitely affect the performance of the query. Why not execute the deletion in a smaller batches to speed up the query execution time? How to delete large data of table in SQL without log?

Answer 3

This is too long for a comment, however, this should be enough to give you the right idea. It seems, however, that the SQL above isn't the full SQL, so I can't give you SQL that would give you the same behaviour (for example you reference a cursor room_cur in your FETCH statements, however, there is no cursor room_cur declared in the SQL).

SQL is a set based language, it excels at set based solutions. Cursors are not set based solutions, they are iterative tasks and SQL Server sucks at it. That is by design. SQL is not a programming language so writing it like a programming language means poor performance.

For a DELETE statement, you just need to treat it like any other statement, as DELETE deletes rows from the defined table in the dataset returned from the FROM. For your DELETE on Users, this (probably) means you want something like this:

DELETE U
FROM dbo.Users U
     JOIN dbo.Account A ON U.acccountID = A.id
WHERE A.[name] IN ('XXXXXX', 'XXXXXX', 'XXXXXX', 'XXXXXX');

This is only DELETE the rows in dbo.Users where a joined row was found in dbo.Account and the value in name is in the IN clause.