我正在尝试使用类似于以下查询的查询来查找两个表(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 性能
发布评论