非常缓慢的DELETE查询

Question

我在SQL性能方面遇到了问题。由于某种突发原因，以下查询非常慢：

我有两个包含特定表的ID的列表。如果第一个列表中的ID已经存在于第二个列表中，我需要删除所有记录。

DECLARE @IdList1 TABLE(Id INT)
DECLARE @IdList2 TABLE(Id INT)

-- Approach 1
DELETE list1
FROM @IdList1 list1
INNER JOIN @IdList2 list2 ON list1.Id = list2.Id

-- Approach 2
DELETE FROM @IdList1
WHERE Id IN (SELECT Id FROM @IdList2)

这两个列表可能包含超过10,000个记录。在这种情况下，每个查询的执行时间都将超过20秒。

执行计划还显示了我不理解的内容。也许这就是为什么它如此缓慢的原因：

我使用10,000个顺序整数填充了两个列表，因此两个列表的起始点都包含值1-10,000。

正如您所看到的，对于@IdList2，实际行数显示为50,005,000!!。 @IdList1是正确的（实际行数为10,000）

我知道有其他解决方案可以解决此问题，例如填充第三个列表而不是从第一个列表中删除。但我的问题是：

为什么这些删除查询如此缓慢，为什么我会看到这些奇怪的查询计划？

- hwcverwe

---

这是一个在现实世界场景中可能会遇到的问题，还是只在这个特定的情况下出现的问题？ - Jodrell

@Jodrell - 表变量没有基于统计数据的重新编译（以及它们上面缺乏有用的索引）的根本问题非常普遍。 - Martin Smith

Answer 1

为您的表变量添加主键，您将会看到它们变得更加高效

DECLARE @IdList1 TABLE(Id INT primary Key not null)
DECLARE @IdList2 TABLE(Id INT primary Key not null)

因为这些表变量上没有索引，任何连接或子查询必须检查大约10,000乘以10,000等于100,000,000个值对。

- Charles Bretana

---

在 @IdList1 上建立索引会有帮助吗？ - Jodrell

任何连接或子查询都必须检查大约10,000乘以10,000 = 100,000,000对值的顺序。这仅适用于嵌套循环。哈希或合并连接将处理每个输入一次（尽管合并连接还需要排序）。 - Martin Smith

@马丁，我已经有一段时间没有读那些东西了，所以我忘记了规则，但是难道不是因为没有索引而选择嵌套循环吗？要做其他循环算法，不需要一个索引来排序值吗？此外，即使使用任何循环算法创建它们，它仍然必须检查每对值 - 例外情况是合并连接，但在那里它必须预先对它们进行排序。 - Charles Bretana

@CharlesBretana - 只要存在等值连接，它就可以使用哈希或合并连接。合并连接将需要对两个输入进行排序（创建索引也是如此），但一旦创建了索引，显然它可能更有用，因为它将有益于其他查询（所以+1）。 - Martin Smith

你的回答和评论与@MartinSmith一起是一个巨大的改进。谢谢！ - hwcverwe

Answer 2

当表变量为空时，SQL Server编译执行计划并在添加行时不重新编译。请尝试。

DELETE FROM @IdList1
WHERE Id IN (SELECT Id FROM @IdList2)
OPTION (RECOMPILE)

这将考虑表变量中实际的行数，并消除嵌套循环计划。

当然，通过约束在 Id 上创建索引对于使用表变量的其他查询也可能有益。

- Martin Smith

---

这对我来说是新的。你能澄清一下吗？当遇到Delete语句时，cacheplan的初始编译会发生，对吗？而不是在声明表变量时？我的意思是，被编译的计划是针对Delete的，而不是针对表变量声明的...如果是这样，那么此时表变量不就被填充了吗？另外，如果您不介意，能否提供一个参考文献？我想阅读一下相关内容。 - Charles Bretana

@CharlesBretana - 这里有一些链接和示例代码，可以在我的回答中找到。 - Martin Smith

然而，由于缓存计划是为每个语句创建的，而不是为整个批处理或存储过程创建的，它是否会在开始执行之前为批处理或过程中的每个语句创建缓存计划？ - Charles Bretana

@CharlesBretana - 在执行语句之前，它会将所有语句编译成批处理，除非该语句引用不存在的对象并被标记为延迟编译。因此，在这种情况下，当表变量为空时，DELETE语句会被编译。然后（由于OPTION (RECOMPILE)），在DELETE点重新编译它，并且可以考虑到表变量填充后的实际行数。 - Martin Smith

你的回答和评论与@CharlesBretana一起是一个巨大的改进。我决定接受Charles的答案，因为我不能接受两个答案;）。谢谢！ - hwcverwe

Answer 3

Id变量中的表格可以拥有主键，因此如果您的数据支持这些的唯一性，那么您可能通过采用来提高性能。请注意保留HTML标签格式。

DECLARE @IdList1 TABLE(Id INT PRIMARY KEY)
DECLARE @IdList2 TABLE(Id INT PRIMARY KEY)

- AakashM

Answer 4

可能的解决方案：

1）尝试创建索引

1.1）如果List{1|2}.Id列具有唯一值，则可以使用PK约束定义唯一聚集索引，如下所示：

DECLARE @IdList1 TABLE(Id INT PRIMARY KEY);
DECLARE @IdList2 TABLE(Id INT PRIMARY KEY);

1.2) 如果List{1|2}.Id列可能存在重复值，则可以使用一个虚拟的IDENTITY列定义一个唯一的聚集索引，如下所示：

DECLARE @IdList1 TABLE(Id INT, DummyID INT IDENTITY, PRIMARY KEY (ID, DummyID) );
DECLARE @IdList2 TABLE(Id INT, DummyID INT IDENTITY, PRIMARY KEY (ID, DummyID) );

2）尝试添加HASH JOIN查询提示，例如：

DELETE list1
FROM @IdList1 list1
INNER JOIN @IdList2 list2 ON list1.Id = list2.Id
OPTION (HASH JOIN);

- Bogdan Sahlean

Answer 5

你正在使用表变量，请给表添加一个主键或将其改为临时表并添加一个索引。这样可以提高性能。一般来说，如果表很小，请使用表变量，但是如果表正在扩展并且包含大量数据，则应该使用临时表。

- Darren

Answer 6

我会想尝试一下

DECLARE @IdList3 TABLE(Id INT);

INSERT @IdList3
SELECT Id FROM @IDList1 ORDER BY Id
EXCEPT
SELECT Id FROM @IDList2 ORDER BY Id

无需删除。

- Jodrell

---

但是如果 OP 需要 删除，就像他/她说的那样：“如果第二个列表中的 Id 已经存在，我需要删除第一个列表中的所有记录”。 - oleksii

@oleksii 是的，OP 表示这是一个人为制造的例子，涉及这两个表变量，特别是删除操作。然而，这对其他读者仍可能有用。 - Jodrell

Answer 7

试试这个替代的语法：

DELETE deleteAlias
FROM @IdList1 deleteAlias
WHERE EXISTS (
        SELECT NULL
        FROM @IdList2 innerList2Alias
        WHERE innerList2Alias.id=deleteAlias.id
    )

编辑.....................

尝试使用带有索引的 #temp 表。

这里提供一个通用示例，其中 "DepartmentKey" 是 PK 和 FK。

IF OBJECT_ID('tempdb..#Department') IS NOT NULL
begin
        drop table #Department
end


CREATE TABLE #Department 
( 
    DepartmentKey int , 
    DepartmentName  varchar(12)
)



CREATE INDEX IX_TEMPTABLE_Department_DepartmentKey ON #Department (DepartmentKey)




IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
begin
        drop table #Employee
end


CREATE TABLE #Employee 
( 
    EmployeeKey int , 
    DepartmentKey int ,
    SSN  varchar(11)
)



CREATE INDEX IX_TEMPTABLE_Employee_DepartmentKey ON #Employee (DepartmentKey)


Delete deleteAlias 
from #Department deleteAlias
where exists ( select null from #Employee innerE where innerE.DepartmentKey = deleteAlias.DepartmentKey )





IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
begin
        drop table #Employee
end

IF OBJECT_ID('tempdb..#Department') IS NOT NULL
begin
        drop table #Department
end

- granadaCoder

---

很不幸，这也很慢。结果相同，查询计划完全相同。 - hwcverwe

你是否被迫使用@variable-tables，还是可以尝试使用#temp表？ - granadaCoder

如果您可以使用 #temp 表，请尝试我的回复中的示例。 - granadaCoder