有人对这两种方法中哪一种更适合大删除有任何建议吗？

Question

方法#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'

方法#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

它们执行不同的操作，在最上面的一项中，您将删除的行数限制为 2000 条符合条件的行。然而，在底部，您将选择限制为返回 2000 行，不管产品 ID，然后仅删除 ProductID = @product_id 的行。底部的具有更多的选择性，并且可以删除更少的行。

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
)