RowNumber() and Partition By performance help wanted <p>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 <code>ROW_NUMBER() OVER (PARTITION BY)</code> BTW I'm still working through the logic, which is currently impossible with this performance issue. Thanks in advance..</p> <p>Edit: I fixed my partition as suggested below.</p> <pre><code>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 &gt; 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) </code></pre>