Does anybody have any suggestions on which of these two approaches is better for large delete?
Posted
by RPS
on Stack Overflow
See other posts from Stack Overflow
or by RPS
Published on 2010-06-08T19:17:46Z
Indexed on
2010/06/08
19:22 UTC
Read the original article
Hit count: 227
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
© Stack Overflow or respective owner
