Question

这里是简化整个储存程序的CTE:

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

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: alt text

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?

Edit: 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 );

我是否还需要就此建立一个单独的统计?

Answer 1

我曾经历过如下情况:<条码>UNIONs>使问题比<条码>慢得多。 UNION ALL with a DISTINCT subsequently. 因此,尽管我没有解释不好的问答计划(统计数字和指数是oka的), 我建议你努力:

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
)

Answer 2

或许,如果你为卡多夫一栏编制统计数据,就会得到改善。

友情链接