为什么一个查询非常慢，但相似表上的相同查询却眨眼间就运行完毕

Question

我有这个查询...运行速度非常慢（几乎一分钟）：

select distinct main.PrimeId 
from PRIME main 
join   
( 
select distinct p.PrimeId   from PRIME p   
left  outer join ATTRGROUP a 
on p.PrimeId = a.PrimeId   or p.PrimeId = a.RelatedPrimeId    
where a.PrimeId is not null and a.RelatedPrimeId is not null  
) mem  
on main.PrimeId = mem.PrimeId

PRIME 表有 18k 行，并且在 PrimeId 上有 PK。

ATTRGROUP 表有 24k 行，并且在 PrimeId、col2、RelatedPrimeId、列 4-7 上有复合 PK。在RelatedPrimeId 上还有一个单独的索引。

该查询最终返回 8.5k 行 - PRIME 表上的 PrimeId 的不同值与 ATTRGROUP 表上的 PrimeId 或 RelatedPrimeId 相匹配

我有相同的查询，使用 ATTRADDRESS 而不是 ATTRGROUP。 ATTRADDRESS 具有与 ATRGROUP 相同的键和索引结构。它只有 11k 行，诚然，这个数字较小，但在这种情况下，查询运行大约一秒，并返回 11k 行。

所以我的问题是：

尽管结构相同，但一个表上的查询怎么会比另一个表慢这么多。

到目前为止，我已经在 SQL 2005 上尝试过这一点，并且（使用相同的数据库，升级）SQL 2008 R2。我们两个人独立获得了相同的结果，将相同的备份恢复到两台不同的计算机上。

其他细节：

• 括号内的位运行时间不到一秒，即使在慢速查询中，
• 执行计划中也有可能的线索，我不明白。这是其中的一部分，其中包含可疑的 320,000,000 行操作：

但是，该表上的实际行数略高于 24k，而不是 320M ！

如果我重构括号内的查询部分，以便它使用 UNION 而不是 OR，因此：

select distinct main.PrimeId 
from PRIME main 
join   
( 
select distinct p.PrimeId   from PRIME p   
left  outer join ATTRGROUP a 
on p.PrimeId = a.PrimeId
where a.PrimeId is not null and a.RelatedPrimeId is not null  
UNION
select distinct p.PrimeId   from PRIME p   
left  outer join ATTRGROUP a 
on p.PrimeId = a.RelatedPrimeId    
where a.PrimeId is not null and a.RelatedPrimeId is not null  
) mem  
on main.PrimeId = mem.PrimeId

... 那么慢速查询需要不到一秒钟的时间。

我非常感谢对此的任何见解！如果您需要更多信息，请告诉我，我会更新问题。谢谢！

顺便说一句，我意识到在这个例子中存在冗余连接。这不能轻易删除，因为在生产中整个事情都是动态生成的，并且括号中的位有许多不同的形式。

---

编辑：

我已经在 ATTRGROUP 上重建了索引，没有显着差异。

编辑2：

如果我使用临时表，则：

select distinct p.PrimeId into #temp
from PRIME p   
left  outer join ATTRGROUP a 
on p.PrimeId = a.PrimeId   or p.PrimeId = a.RelatedPrimeId    
where a.PrimeId is not null and a.RelatedPrimeId is not null  

select distinct main.PrimeId 
from Prime main join   
#temp mem  
on main.PrimeId = mem.PrimeId

...话又说回来，即使在原始 OUTER JOIN 中使用 OR，它也会在不到一秒的时间内运行。我讨厌像这样的临时表，因为它总是感觉像是承认失败，所以这不是我将使用的重构，但我认为它产生这样的差异很有趣。

编辑 3：

更新统计数据也没有什么区别。

感谢您迄今为止的所有建议。

Answer 1

根据我的经验，最好在 JOIN 子句中使用两个左连接而不是 OR。
所以

    left  outer join ATTRGROUP a 
    on p.PrimeId = a.PrimeId   or p.PrimeId = a.RelatedPrimeId

我建议：

    left  outer join ATTRGROUP a 
    on p.PrimeId = a.PrimeId
    left  outer join ATTRGROUP a2
    on p.PrimeId = a2.RelatedPrimeId    

Answer 2

我注意到主查询与子查询不相关：

select distinct main.PrimeId 
from PRIME main 
join   
( 
select distinct p.PrimeId   from PRIME p   
left  outer join ATTRGROUP a 
on p.PrimeId = a.PrimeId
where *main.PrimeId = a.PrimeId*  
UNION
select distinct p.PrimeId   from PRIME p   
left  outer join ATTRGROUP a 
on p.PrimeId = a.RelatedPrimeId    
where *main.PrimeId = a.PrimeId*  
) mem  
on main.PrimeId = mem.PrimeId

在这种构造中，您也不需要使用“is not null”子句（您是否需要它，因为主键永远不会保留空值） -价值？）。

我被教导要避免 OR 结构（正如其他人已经建议的那样），但也要避免“is not null”或“in valuelist”结构。这些大多可以用 (NOT) EXISTS 子句代替。

Answer 3

这不是直接答案，但如果您有从 ATTRGROUP.PrimeId 和 ATTRGROUP.RelatedPrimeId 引用到 main 的 FK 约束，那么您的查询相当于简单得多的查询：

select PrimeId   from ATTRGROUP a 
union
select RelatedPrimeId from ATTRGROUP a 

Answer 4

一个表上的一个查询可能比另一个表上的查询慢得多的原因之一是该表上的统计信息已过时，并且它选择了错误的查询计划。

不过，我支持重构，摆脱其他人建议的 or 子句。