为什么一个查询非常慢，但在类似的表上相同的查询在眨眼间运行

I have this query ...which runs extremely slowly (almost a minute):

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

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

The PRIME table has 18k rows, and has PK on PrimeId.

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

The ATTRGROUP table has 24k rows, and has a composite PK on PrimeId, col2, then RelatedPrimeId, and then cols 4-7. There's also a separate index on RelatedPrimeId.

ATTRGROUP表有24k行，在PrimeId，col2上有复合PK，然后是RelatedPrimeId，然后是cols 4-7。 RelatedPrimeId上还有一个单独的索引。

The query eventually returns 8.5k rows - distinct values of PrimeId on the PRIME table that match either PrimeId or RelatedPrimeId on the ATTRGROUP table

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

I have the identical query, using ATTRADDRESS instead of ATTRGROUP. ATTRADDRESS has an identical key and index structure as ATTRGROUP. It has only 11k rows on it, which is smaller, admittedly, but in that case, the query runs in about a second, and returns 11k rows.

我有相同的查询，使用ATTRADDRESS而不是ATTRGROUP。 ATTRADDRESS具有与ATTRGROUP相同的密钥和索引结构。它只有11k行，不过可以肯定它是小的，但在这种情况下，查询运行大约一秒钟，并返回11k行。

So my question is this:

所以我的问题是：

How can the query be so much slower on one table than another, despite the structures being identical.

尽管结构相同，但查询如何在一个表上比另一个表慢得多。

So far, I've tried this on SQL 2005, and (using the same database, upgraded) SQL 2008 R2. Two of us have independently obtained the same results, restoring the same backup to two different computers.

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

Other details:

其他详情：

• the bit inside the brackets runs in less than a second, even in the slow query
• 即使在慢速查询中，括号内的位也会在不到一秒的时间内运行
• there's a possible clue in the execution plan, which I don't understand. Here's part of it, with a suspicious 320,000,000 row operation:
• 执行计划中可能存在一些线索，我不明白。这是它的一部分，有可疑的320,000,000行操作：

However, the actual number of rows on that table is a little over 24k, not 320M !

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

If I refactor the part of the query inside the brackets, so that it uses a UNION rather than an OR, thus:

如果我在括号内重构查询的一部分，那么它使用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

... then the slow query takes under a second.

...然后慢速查询需要一秒钟。

I'd greatly appreciate any insight on this! Let me know if you need any more info and I'll update the question. Thanks!

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

By the way, I realise that in this example there's a redundant join. This can't easily be removed, since in production the whole thing is generated dynamically, and the bit in the brackets takes many different forms.

顺便说一句，我意识到在这个例子中有一个冗余连接。这不容易被删除，因为在生产中，整个事物是动态生成的，括号中的位采用许多不同的形式。

---

Edit:

编辑：

I've rebuilt the indexes on ATTRGROUP, makes no significant difference.

我在ATTRGROUP上重建了索引，没有太大的区别。

Edit 2:

编辑2：

If I use a temporary table, thus:

如果我使用临时表，那么：

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

... then again, even with an OR in the original OUTER JOIN, it runs in less than a second. I hate temp tables like this, since it always feels like an admission of defeat, so it isn't the refactor I'll be using, but I thought it was interesting that it makes such a difference.

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

Edit 3:

编辑3：

Updating the stats makes no difference either.

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

Thanks for all your suggestions so far.

感谢您到目前为止的所有建议。

4 个解决方案

#1

In my experience its better to use two left joins rather than an OR in the JOIN clause. So instead of:

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

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

I would suggest:

我会建议：

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

#2

I notice that the main-query isn't correlated with the sub-query:

我注意到主查询与子查询没有关联：

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

In this construction you don't need to use the 'is not null' clause as well (will you ever need that since a primarykey will never hold a null-value?).

在这种结构中，你也不需要使用'is not null'子句（你是否需要它，因为主键永远不会保存空值？）。

I was taught to avoid OR-constructions (as is already adviced by others) but also to avoid 'is not null' or 'in valuelist' - construction. Those can mostly be replaced by an (NOT) EXISTS-clause.

我被教导要避免OR构造（正如其他人已经建议的那样），但也要避免“不是空”或“在估价中” - 构造。这些可以大部分被（NOT）EXISTS子句替换。

#3

This is not a direct answer, but if you have FK constraints referring from ATTRGROUP.PrimeId and ATTRGROUP.RelatedPrimeId to main, then your query is equivalent to this much simpler one:

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

select PrimeId   from ATTRGROUP a 
union
select RelatedPrimeId from ATTRGROUP a 

#4

One reason why one query could be much slower on one table than the other is that statistics on that table are out of date and it is choosing the wrong query plan.

一个查询在一个表上的速度比另一个表慢得多的一个原因是该表上的统计信息已过期，并且选择了错误的查询计划。

However I support the refactoring that gets rid of the or clause that others have suggested anyway.

但是，我支持重构，摆脱其他人建议的或者条款。