0

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..

Edit: 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)
StatsViaCsh
  • 2,600
  • 10
  • 44
  • 63
  • What is the execution plan, and do you have any indexes on the tables? – ta.speot.is Aug 24 '12 at 22:51
  • 1
    Self referencing CTE's are slow. Use temporary tables. http://stackoverflow.com/a/2741802/284240 – Tim Schmelter Aug 24 '12 at 22:53
  • I do have a unique clustered index on the table.. I put it to the database engine tuning advisor, and it makes no recommendations.. do you want the execution plan xml? – StatsViaCsh Aug 24 '12 at 23:08
  • The first CTE doesn't really help. What is the meaning of "period"? It's difficult to grok the second CTE in fullness without understanding "period". My personal preference: When I join a table to itself I generally use `L` and `R` as aliases ("left" and "right") or prefix the aliases with "L" and "R", e.g. `LsmaCBP` and `RsmaCBP`. If there is some specific semantic reason for the join then I'll use that, e.g. `ParentOrganism` and `ChildOrganism`. – HABO Aug 24 '12 at 23:29
  • @HABO Thx. "Period" is a number of days, 5,10,20 etc that describe the moving average value. If that helps. – StatsViaCsh Aug 24 '12 at 23:36
  • Is there a covering index on `Symbol`, `TradeDate` and `Period` with `Value` as an included column? – HABO Aug 25 '12 at 00:46
  • @HABO I do. I'm trying Tim's suggestion now using temporary tables. Now I wonder if I should create index(es) within the script.. – StatsViaCsh Aug 25 '12 at 01:24
  • While I failed to sleep last night I was pondering whether indexing the partitioned data on a composite key of `Symbol` and `RowNum` (desc?) would pay back the indexing overhead. Sounds like you have some experiments to run. – HABO Aug 25 '12 at 14:09

1 Answers1

0

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) 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)

Yván Ecarri
  • 1,661
  • 18
  • 39