# SQL Server index / performance help needed (index scan and a sort taking 40 minutes)

I finally have the logic figured out for a query, now I need to speed it up... dramatically, if possible (it runs for 40 minutes +/-). It's on two tables, one with just a couple hundred rows (tblTradingDays) and the other with over a million (tblDailySMA). It returns 48 million rows.

My basic logic is: I'm returning a row for each combination of symbol, trade date, and period values, creating a "slow v fast" pair of periods. So in the original table, there is a symbol, a trade date, a period (5,10,20,...) and the actual value for that combination. I want to compare the values for all combinations of periods (except period p1 = period p2). I'm numbering the records to be used for further processing. Hope that makes sense.

I've tried various indexes and combinations of the fields involved in the joins and predicates. In the estimated execution plan, it looks like a sort is the most expensive operation. The only index on tblDailySMA that appears in the plan, despite my adding others is nonclustered, unique, on: Symbol, TradeDate, Period, and includes Value. It is scanned. The query is below, hopefully someone can help me out. Thanks in advance..

SELECT
ROW_NUMBER() OVER
(
ORDER BY t1.Symbol, t1.Period, t2.Period, t.TradingDate DESC
) RowNum,
t1.Symbol, t.TradingDate, t1.Period, t2.Period, t1.Value FastValue,
t2.Value SlowValue, (t1.Value - t2.Value) SlowFastDiff,
ChgSign = CASE WHEN t1.Value < t2.Value THEN 0
WHEN t1.Value >= t2.Value THEN 1
WHEN t1.Value IS NULL OR t2.Value IS NULL THEN NULL
END
FROM
RIGHT JOIN
INNER JOIN
WHERE
t1.Period < t2.Period


Here's the execution plan:

  |--Compute Scalar(DEFINE:([Expr1007]=[Market].[dbo].[tblDailySMA].[Value] as [t1].[Value]-[Market].[dbo].[tblDailySMA].[Value] as [t2].[Value], [Expr1008]=CASE WHEN [Market].[dbo].[tblDailySMA].[Value] as [t1].[Value]<[Market].[dbo].[tblDailySMA].[Value] as [t2].[Value] THEN (0) ELSE CASE WHEN [Market].[dbo].[tblDailySMA].[Value] as [t1].[Value]>=[Market].[dbo].[tblDailySMA].[Value] as [t2].[Value] THEN (1) ELSE NULL END END))
|--Sequence Project(DEFINE:([Expr1006]=row_number))
|--Segment
|--Parallelism(Gather Streams, ORDER BY:([t1].[Symbol] ASC, [t1].[Period] ASC, [t2].[Period] ASC, [t].[TradingDate] DESC))
|--Sort(ORDER BY:([t1].[Symbol] ASC, [t1].[Period] ASC, [t2].[Period] ASC, [t].[TradingDate] DESC))
|--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([t].[TradingDate]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([t1].[TradeDate]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([t1].[Symbol], [t1].[TradeDate]), ORDER BY:([t1].[Symbol] ASC, [t1].[TradeDate] ASC))
|    |--Index Scan(OBJECT:([Market].[dbo].[tblDailySMA].[IX_tblDailySMA_Noncl_SymbTrDatePer] AS [t1]), ORDERED FORWARD)
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([t2].[Symbol], [t2].[TradeDate]), ORDER BY:([t2].[Symbol] ASC, [t2].[TradeDate] ASC))
|--Index Scan(OBJECT:([Market].[dbo].[tblDailySMA].[IX_tblDailySMA_Noncl_SymbTrDatePer] AS [t2]), ORDERED FORWARD)

-

## migrated from stackoverflow.comAug 30 '12 at 23:13

This question came from our site for professional and enthusiast programmers.

Can you post your execution plan? –  mwigdahl Aug 30 '12 at 17:08
You might also explain what you're trying to do. The structure you have here is a triangular join, which doesn't scale well at all with large tables. –  mwigdahl Aug 30 '12 at 17:20
You start out with the largest table, then join smaller tables to it. –  Kermit Aug 30 '12 at 18:10
What do you need tblTradingDays for? Looks like it's not doing anything. Or maybe you want to LEFT JOIN instead of RIGHT JOIN? –  Yuxiu Li Aug 30 '12 at 18:30
@YuxiuLi Good catch, I just fixed that... the data in tblDailySMA is missing a few values, so I do use a left outer join to keep it strict. –  StatsViaCsh Aug 30 '12 at 18:41

When I have this kind of performance problems in a big query I split it into small queries with temporary tables. For me it is a solution and performance ratio may be 10 to 1 or more.

First step:

with t1 as (
SELECT
t1.Symbol,
t1.Period,
t1.Value as FastValue
FROM      tblDailySMA t1
)
select *
into #t1
from t1;

--I include period into idex to avoid table access on next query
create index t1_idx on ( Symbol, TradeDate, Period )


Secon step:

with t2 as (
SELECT
t1.Symbol,
t1.Period as period_t1,
t1.Value as FastValue
t2.Period as period_t2,
t2.Value as SlowValue,
FROM       #t1 as t1
INNER JOIN tblDailySMA t2
WHERE t1.Period < t2.Period
)
select
*
into #t2
from t2;

--Here create indexes for t2
--Here next and final query


And so on. One benefit of this system is that you can improve queries step by step.

-
Temp table can easily increase the log file very quickly? –  testing Aug 30 '12 at 17:30