SQL EXCEPT 性能

Question

我正在尝试使用类似以下查询的查询语句，以查找两个表（DEV数据库中的相同表与TEST数据库中的相同表）之间的差异。每个表都有大约30K行和5列。

select field1,field2,field3,field4,field5 from dev.dbo.table1 
where field1+field2 in ('string1','string2','string3',...,'string50')
except
select field1,field2,field3,field4,field5 from test.dbo.table1 
where field1+field2 in ('string1','string2','string3',...,'string50')

字段1是char(5)，字段2是char(1)

这个查询基本上永远不会终止。

当我使用SET SHOWPLAN_ALL ON分析这个查询时，我可以看到树中有一个嵌套循环。当我将上述查询更改为

select * from dev.dbo.table1 
except
select * from test.dbo.table2

查询运行速度快，执行计划中没有嵌套循环。

可以有人帮忙解释一下吗？我不明白为什么会有如此大的差异。

- Noble Shore

---

微软 SQL Server 2005 - Noble Shore

Answer 1

我的最佳猜测是优化器对两个表的基数（大小）估计不太准确。由于低估了大小，因此会生成一个较差的查询计划。

在SQL Server中，您可以在except上使用join提示。因此，您可以使用以下查询获得所需结果：

select field1,field2,field3,field4,field5 from dev.dbo.table1 
where field1+field2 in ('string1','string2','string3',...,'string50')
except
select field1,field2,field3,field4,field5 from test.dbo.table1 
where field1+field2 in ('string1','string2','string3',...,'string50')
option (hash join, merge join)

这将排除嵌套循环连接的选项，选择更有利的方法。

- Gordon Linoff

---

我已确认这确实是情况。当针对其他数据库执行相同的查询时，不会出现嵌套循环，因此故障似乎在于SQL Server。 - Noble Shore

Answer 2

你的第一个查询很慢，因为你在where子句中连接字段，这本质上是一个函数。几乎每次在where子句中运行函数时都会发生这种情况。这里有一个更简单的例子。这将很快。

where myDateTimeField >= @DateValue
and myDateTimeField < dateadd(day, 1, @DateValue)

这在逻辑上是相同的，但会很慢。

where cast(myDateTimeField as date) = @DateValue

- Dan Bracuk

---

谢谢，我在两个数据库中选择了table1并将其存储到临时表中，同时添加了一个列field1+field2。然后修改了查询以使用临时表，这解决了我的问题。 - Noble Shore

Answer 3

我将为那些对此感兴趣的人写下这篇答案，我知道这个话题已经快10年了，但是我自己也遇到了这个问题，却找不到答案。

没错，情况就是这样，我的建议是使用INNER JOIN而不是EXCEPT，虽然代码会变得有点长，但性能值得。

以下是我的解决方案：

SELECT 
t1.a,
t1.b
FROM @Tabl1 t1

EXCEPT

SELECT 
t2.a,
t2.b
FROM @Tabl2 t2

对于超过1k条记录的表格，性能非常差。

SQL中EXCEPT关键字的性能

以下是我的解决方案：

SELECT 
    t1.a,
    t1.b,
    t1.c,
    t1.d
FROM @StoreConfig t1
JOIN  (SELECT  
        t2.a,
        t2.b,
        t2.c,
        t2.d
FROM Table2 t2 ON 
    t2.a= t1.a AND t2.b = t1.b AND( t2.c <> t1.c OR t2.d <> t1.d)

这里展示了性能的提升（请注意，没有最后一个选择所有请求需要约 1 秒钟，因此此联接只需花费 0.01-0.3 秒，而不是整个 4 秒。

使用 JOIN 语句的性能

- Andrey Argiziont2