Does anybody have any suggestions on which of these two approaches is better for large delete?
- by RPS
Approach #1:
DECLARE @count int
SET @count = 2000
DECLARE @rowcount int
SET @rowcount = @count
WHILE @rowcount = @count BEGIN
DELETE TOP (@count) FROM ProductOrderInfo
WHERE ProductId = @product_id
AND bCopied = 1 AND FileNameCRC = @localNameCrc
SELECT @rowcount = @@ROWCOUNT
WAITFOR DELAY '000:00:00.400'
Approach #2:
DECLARE @count int
SET @count = 2000
DECLARE @rowcount int
SET @rowcount = @count
WHILE @rowcount = @count BEGIN
DELETE FROM ProductOrderInfo
WHERE ProductId = @product_id AND FileNameCRC IN
(
SELECT TOP(@count) FileNameCRC
FROM ProductOrderInfo WITH (NOLOCK)
WHERE bCopied = 1 AND FileNameCRC = @localNameCrc
)
SELECT @rowcount = @@ROWCOUNT
WAITFOR DELAY '000:00:00.400'
END
