CODEWITHSUNDEEP
sql-serverwhile-looptransactionssql-delete
Prakash
Prakash

Reputation: 555

DELETE using WHILE LOOP in a TRANSACTION

I am trying to delete table which just has around 39K records, but for some reasons it is taking time(Around 1.5 minutes) even to delete a single record. How can I improve the performance of my delete operation. How can I ensure that log activity is not taking much time. Can I put the "DELETE" statement within a while loop and then open a transaction and commit it each time it successfully completes. Any other effective method is available?

[PrimaryKey] here has a "Clustered Index"

DECLARE @BatchCount INT;
SELECT @BatchCount  = COUNT(1) FROM #DHDID

DECLARE @Counter INT = 1

WHILE( @Counter <= @BatchCount)
BEGIN

BEGIN TRANSACTION

DECLARE @ID INT;

SELECT @ID = DHDID FROM #DHDID WHERE ID = @Counter

DELETE FROM <MYTABLE> WHERE [PrimaryKey] = @ID

COMMIT TRANSACTION

SET @Counter = @Counter + 1

END

Upvotes: 1

Views: 5341

Answers (2)

Bartosz Siemasz
Bartosz Siemasz

Reputation: 306

I would try creating index on the #DHDID table:

CREATE NONCLUSTERED INDEX [idx] ON [#DHDID] ([ID] ASC) INCLUDE ([DHDID])

Upvotes: 1

Brian
Brian

Reputation: 1248

Based on your answer, you should do a set-based delete via a join. Try something like this:

Begin Tran
Delete m
    From <MyTable> m
        Inner Join DHDID d
            on d.DHDID = m.[PrimaryKey]

-- error detection code here
If <an error occurred>
    Rollback
Else
    Commit

Upvotes: 2

Related Questions