SQL Server:UNION 后的 INNER JOIN 导致哈希匹配缓慢(聚合) [英] SQL Server: INNER JOIN after UNION leads to slow Hash Match (Aggregate)

问题描述

这是一个减慢整个存储过程的 CTE:

Here is a CTE that slows down the whole stored procedure:

select * 
from #finalResults
where intervalEnd is not null
union
select            
    two.startTime, 
    two.endTime,
    two.intervalEnd,
    one.barcodeID,
    one.id,
    one.pairId,
    one.bookingTypeID,
    one.cardID,
    one.factor,
    two.openIntervals,
    two.factorSumConcurrentJobs
from #finalResults as one
inner join #finalResults as two
    on  two.cardID = one.cardID
    and two.startTime > one.startTime
    and two.startTime < one.intervalEnd

表#finalResults 包含超过 600K 行，UNION 的上半部分(where intervalEnd is not null)大约 580K 行，下半部分与连接的 #finalResults 大约 300K 行.然而，这个内部连接估计最终会达到 100 mio.行，这可能负责长时间运行的哈希匹配:

The table #finalResults contains a little over 600K lines, the upper part of the UNION (where intervalEnd is not null) about 580K rows, the lower part with the joined #finalResults roughly 300K rows. However, this inner join estimates to end up with a whooping 100 mio. rows, which might be responsible for the long-running Hash Match here:

现在，如果我理解哈希联接，较小的表应该首先散列并插入更大的表，如果你一开始猜错了大小，你会因中间过程角色逆转而受到性能损失.这可能是造成缓慢的原因吗?
我尝试了显式的 inner merge join 和 inner loop join 以希望提高行数估计，但无济于事.
另一件事:右下角的 Eager Spool 估计有 17K 行，最终有 300K 行，并执行了近 50 万次重新绑定和重写.这正常吗?

Now if I understand Hash Joins correctly, the smaller table should be hashed first and the larger table inserted, and if you guess the sizes wrong at first, you get performance penalties due to mid-process role reversal. Might this be responsible for the slowness?
I tried an explicit inner merge join and inner loop join in hopes of improving the row count estimate, but to no avail.
One other thing: the Eager Spool on the bottom right estimates 17K rows, ends up with 300K rows and performs almost half a million rebinds and rewrites. Is this normal?

临时表 #finalResults 上有一个索引:

The temp table #finalResults has an index on it:

create nonclustered index "finalResultsIDX_cardID_intervalEnd_startTime__REST"
on #finalresults( "cardID", "intervalEnd", "startTime" )
include( barcodeID, id, pairID, bookingTypeID, factor,
         openIntervals, factorSumConcurrentJobs );

我是否还需要在其上建立单独的统计数据?

Do I need to build a separate statistic on it as well?

推荐答案

我遇到过这样的情况，UNION 的查询比 UNION ALL 用 DISTINCT 之后.所以虽然我对糟糕的查询计划没有解释(统计和索引可以吗?)，但我建议您尝试以下操作:

I have experienced situations where UNION's made a query much slower than UNION ALL with a DISTINCT afterwards. So while I don't have an explanation for the bad query plan (statistics and indexes are okay?), I suggest that you try the following:

select distinct * from (
    select * 
    from #finalResults
    where intervalEnd is not null
    union all
    select            
        two.startTime, 
        two.endTime,
        two.intervalEnd,
        one.barcodeID,
        one.id,
        one.pairId,
        one.bookingTypeID,
        one.cardID,
        one.factor,
        two.openIntervals,
        two.factorSumConcurrentJobs
    from #finalResults as one
    inner join #finalResults as two
        on  two.cardID = one.cardID
        and two.startTime > one.startTime
        and two.startTime < one.intervalEnd
)

这篇关于SQL Server:UNION 后的 INNER JOIN 导致哈希匹配缓慢(聚合)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！