How to Delete Large Amounts of Data

Question

Post reply

How to Delete Large Amounts of Data

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11381 More actions · Answer 1

hmbacon wrote:

I believe that the fast forward cursor of SQL Server is a great tool to use if, and only if, multiple actions need to be taken on each record returned.

Opening Pandora's box on this one. I can't remember the last time I used one. Almost all code that needs one can be resolved with a set-based solution, but there are the outliers.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

hmbacon SSC Journeyman Points: 77 More actions · Answer 2

hmbacon

SSC Journeyman

Points: 77

October 19, 2021 at 8:19 pm

#3942138

I indicated that the need for a cursor is rare but you need to be willing to use them. If you need to update 6 tables based on the data in a 7th table, SQL Server does not have a multi-table update statement. How would you do this with 1 set based statement?

MMartin1 One Orange Chip Points: 27655 More actions · Answer 3

I would not fixate on sticking to one set based statement as I would to transactions. Each loop in a cursor can be thought of as a transaction . Where as I could update multiple tables with parallel transactions from one source (no exclusive lock needed).

Such a use case would be updating multiple test environments to be in sync.

----------------------------------------------------

Thomas Franz Hall of Fame Points: 3760 More actions · Answer 4

@hmbacon: you could either do 6 Updates each joining the base table or - if the select of the base table is slow / difficult - write the ids, that needs to be updated into a #tmp and join the #tmp

@mike01:

When you have to work with 100 mio rows you could do it with a set based statement, which runs 3 hours (bad when it fails for whatever reasons) or break it up into smaller chunks of maybe 500k rows. Of course it would cripple your system when you would write a cursor that perfoms single row actions or with only 10 rows in this case, but the "smoother" running of a good sized cursor vs. a gigantic monolith statement oftens makes it a viable solution.

God is real, unless declared integer.

Phil Parkin SSC Guru Points: 245813 More actions · Answer 5

MMartin1 wrote:

Each loop in a cursor can be thought of as a transaction

..Can be thought of? Are you saying that if a loop in a cursor contains multiple actions, and any one of those actions fails, that all of the other actions are rolled back? The following code suggests not:

DROP TABLE IF EXISTS #TranTest;

CREATE TABLE #TranTest
(
    n INT NOT NULL
);

INSERT #TranTest
(
    n
)
VALUES
(1  )
,(2)
,(3);

DECLARE @n INT;

DECLARE test_cursor CURSOR STATIC FORWARD_ONLY READ_ONLY LOCAL FOR
SELECT tt.n
FROM #TranTest tt;

OPEN test_cursor;

BEGIN TRY
    FETCH NEXT FROM test_cursor
    INTO @n;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        --BEGIN TRAN;

        INSERT #TranTest
        (
            n
        )
        VALUES
        (@n + 10);

        IF @n = 2
            PRINT 1 / 0;

        --COMMIT;

        FETCH NEXT FROM test_cursor
        INTO @n;
    END;

    CLOSE test_cursor;
    DEALLOCATE test_cursor;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK;

    SELECT *
    FROM #TranTest tt;

    CLOSE test_cursor;
    DEALLOCATE test_cursor;
END CATCH;

If you uncomment the BEGIN TRAN and COMMIT rows and run this, the value '12' does not appear in the final result set.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

MMartin1 One Orange Chip Points: 27655 More actions · Answer 6

THank you for that piece of information. However my comment centered specifially on the post about inserting into multiple tables from one source , and how that can be done in parrallel (each its own transaction) . This is similar, though not exact , do using a cursor to achieve the same outcome. Hence the absrtaction "can be thought of" in this context.

----------------------------------------------------