SQL EXCEPT 性能 [英] SQL EXCEPT performance

问题描述

我正在尝试使用类似于以下查询的查询来查找两个表(DEV 数据库与 TEST 数据库中的同一个表)之间的差异.每个表有 ~30K 行和 ~5 列.

I'm trying to use a query similar to the following query to find differences between two tables (the same table in a DEV database vs a TEST database). Each table has ~30K rows and ~5 columns.

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')

field1 是 char(5) 和 field2 是 char(1)

field1 is char(5) and field2 is char(1)

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

This query essentially never terminates.

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

When i analyze this query using SET SHOWPLAN_ALL ON, I can see there is a nested loop pretty high in the tree. When I change the above query to

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

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

The query runs quickly and there is no nested loop in the execution plan.

有人可以帮忙解释一下吗?我不明白为什么会有很大的不同.

Can someone help explain this? I don't understand why there would be a drastic difference.

推荐答案

我最好的猜测是优化器在估计两个表的基数(大小)方面做得很差.因为它低估了大小，所以它生成了一个糟糕的查询计划.

My best guess is that the optimizer is doing a poor job of estimating the cardinality (size) of the two tables. Because it underestimates the size, it is generating a poor query plan.

在 SQL Server 中，您可以对 except 使用 join 提示.因此，您可以获得所需的查询:

In SQL Server, you can use join hints on except. So, you can get the query you want with:

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)

这消除了嵌套循环连接的选项，选择了更有利的方法.

This eliminates the option of the nested loop join, choosing a more favorable method.

这篇关于SQL EXCEPT 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！