Does anybody have any suggestions on which of these two approaches is better for large delete?

Question

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

Answer 1

They do separate things, in the top one you're limiting your delete to 2000 rows that match the criteria. In the bottom one however, you're limiting a select to return 2000 rows, despite product id, and then deleting only the ones where ProductID = @product_id. The bottom one has more-selectivity and the potential to delete fewer rows.

DELETE FROM ProductOrderInfo
WHERE ProductId = @product_id AND FileNameCRC IN 
(
  -- Now if @count is 2000
  -- You're guarentted *at most* 2000 rows
  -- *none* of which are guaranteed to have `ProductId = @product_id`

  SELECT TOP(@count) FileNameCRC
  FROM ProductOrderInfo WITH (NOLOCK)
  WHERE bCopied = 1 AND FileNameCRC = @localNameCrc
)