Suth (10/28/2016)

---

Hi,

I'm currently trying delete data from a large database with the below code... But its taking quite a long time to delete 1000 rows the table holds over 1mil records

Any ideas on how I can improve the performance ?

The column is indexed and i have tired using rowlock but still no improvement

DECLARE @r INT;

DECLARE @loopCount int;

DECLARE @maxLoops int;

SET @maxLoops = 5

SET @loopCount = 0

SET @r = 1;

WHILE @r > 0 AND @loopCount < @maxLoops

BEGIN

DELETE TOP (1000)

FROM [u_od] WITH (ROWLOCK)

WHERE

odid IN (SELECT id FROM contacts)

SET @r = @@ROWCOUNT;

SET @loopCount = @loopCount + 1;

WAITFOR DELAY '00:00:01'

END

GO