RowNumber() 和 Partition By 性能需要帮助 [英] RowNumber() and Partition By performance help wanted

问题描述

我有一张股票市场移动平均值表，我试图比较一天内的两个值，然后将该值与前一天的相同计算值进行比较.我的 sql 就在下面......当我注释掉定义结果集的最后一个选择语句，并运行显示为结果集的最后一个 cte 时，我在大约 15 分钟内取回我的数据.很长，但易于管理，因为它会在一夜之间作为插入 sproc 运行.当我如图所示运行它时，我在 40 分钟之前甚至开始出现任何结果.有什么想法吗?它从有点慢，到爆炸，可能是添加了 ROW_NUMBER() OVER (PARTITION BY) 顺便说一句，我仍在研究逻辑，目前无法解决此性能问题.提前致谢..

I've got a table of stock market moving average values, and I'm trying to compare two values within a day, and then compare that value to the same calculation of the prior day. My sql as it stands is below... when I comment out the last select statement that defines the result set, and run the last cte shown as the result set, I get my data back in about 15 minutes. Long, but manageable since it'll run as an insert sproc overnight. When I run it as shown, I'm at 40 minutes before any results even start to come in. Any ideas? It goes from somewhat slow, to blowing up, probably with the addition of ROW_NUMBER() OVER (PARTITION BY) BTW I'm still working through the logic, which is currently impossible with this performance issue. Thanks in advance..

我按照下面的建议修复了我的分区.

I fixed my partition as suggested below.

with initialSmas as
(
    select TradeDate, Symbol, Period, Value
    from tblDailySMA
),

smaComparisonsByPer as
(
    select i.TradeDate, i.Symbol, i.Period FastPer, i.Value FastVal, 
        i2.Period SlowPer, i2.Value SlowVal, (i.Value-i2.Value) FastMinusSlow
    from initialSmas i join initialSmas as i2 on i.Symbol = i2.Symbol 
        and i.TradeDate = i2.TradeDate and i2.Period > i.Period
),

smaComparisonsByPerPartitioned as
(
    select ROW_NUMBER() OVER (PARTITION BY sma.Symbol, sma.FastPer, sma.SlowPer
    ORDER BY sma.TradeDate) as RowNum, sma.TradeDate, sma.Symbol, sma.FastPer,
    sma.FastVal, sma.SlowPer, sma.SlowVal, sma.FastMinusSlow
    from smaComparisonsByPer sma
)

select scp.TradeDate as LatestDate, scp.FastPer, scp.FastVal, scp.SlowPer, scp.SlowVal,
    scp.FastMinusSlow, scp2.TradeDate as LatestDate, scp2.FastPer, scp2.FastVal, scp2.SlowPer, 
    scp2.SlowVal, scp2.FastMinusSlow, (scp.FastMinusSlow * scp2.FastMinusSlow) as Comparison
from smaComparisonsByPerPartitioned scp join smaComparisonsByPerPartitioned scp2
on scp.Symbol = scp2.Symbol and scp.RowNum = (scp2.RowNum - 1)

推荐答案

1) 在 Partition By 和 Order By 子句中都有一些字段.这是没有意义的，因为每个值(sma.FastPer、sma.SlowPer)只有一个且只有一个.您可以安全地从窗口函数的 Order By 部分删除这些字段.

1) You have some fields both in the Partition By and the Order By clauses. That doesn't make sense since you will have one and only one value for each (sma.FastPer, sma.SlowPer). You can safely remove these fields from the Order By part of the window function.

2) 假设您已经在initialSmas i join initialSmas"中拥有足够性能的索引，并且您已经拥有 (initialSmas.Symbol, initialSmas.Period, initialSmas.TradeDate) 的索引，您可以做的最好的事情就是复制smaComparisonsByPer 到一个临时表中，您可以在其中创建索引 (sma.Symbol, sma.FastPer, sma.SlowPer, sma.TradeDate)

2) Assuming that you already have indexes for adequate performance in "initialSmas i join initialSmas" and that you already have and index for (initialSmas.Symbol, initialSmas.Period, initialSmas.TradeDate) the best you can do is to copy smaComparisonsByPer into a temporary table where you can create an index on (sma.Symbol, sma.FastPer, sma.SlowPer, sma.TradeDate)

这篇关于RowNumber() 和 Partition By 性能需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！