Long-running Database Query

Question

I have a long-running SQL Server 2005 query that I have been hoping to optimize.

When I look at the actual execution plan, it says a Clustered Index Seek has 66% of the cost.

Execuation Plan Snippit:

<RelOp AvgRowSize="31" EstimateCPU="0.0113754" EstimateIO="0.0609028" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10198.5" LogicalOp="Clustered Index Seek" NodeId="16" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0722782">
                              <OutputList>
                                <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="quoteDate" />
                                <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="price" />
                                <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="tenure" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="1067" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="quoteDate" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="price" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="tenure" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Index="[_dta_index_Indices_14_320720195__K5_K2_K1_3]" Alias="[I]" />
                                <SeekPredicates>
                                  <SeekPredicate>
                                    <Prefix ScanType="EQ">
                                      <RangeColumns>
                                        <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="HedgeProduct" ComputedColumn="true" />
                                      </RangeColumns>
                                      <RangeExpressions>
                                        <ScalarOperator ScalarString="(1)">
                                          <Const ConstValue="(1)" />
                                        </ScalarOperator>
                                      </RangeExpressions>
                                    </Prefix>
                                    <StartRange ScanType="GE">
                                      <RangeColumns>
                                        <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="tenure" />
                                      </RangeColumns>
                                      <RangeExpressions>
                                        <ScalarOperator ScalarString="[@StartMonth]">
                                          <Identifier>
                                            <ColumnReference Column="@StartMonth" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </RangeExpressions>
                                    </StartRange>
                                    <EndRange ScanType="LE">
                                      <RangeColumns>
                                        <ColumnReference Database="[wf_1]" Schema="[dbo]" Table="[Indices]" Alias="[I]" Column="tenure" />
                                      </RangeColumns>
                                      <RangeExpressions>
                                        <ScalarOperator ScalarString="[@EndMonth]">
                                          <Identifier>
                                            <ColumnReference Column="@EndMonth" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </RangeExpressions>
                                    </EndRange>
                                  </SeekPredicate>
                                </SeekPredicates>
                              </IndexScan>
                            </RelOp>

From this, does anyone see an obvious problem that would be causing this to take so long?

Here is the query:

(SELECT quotedate, tenure, price, ActualVolume, HedgePortfolioValue, Price AS UnhedgedPrice, ((ActualVolume*Price - HedgePortfolioValue)/ActualVolume) AS HedgedPrice
        FROM
            (
            SELECT [quoteDate]
                  ,[price]
                  , tenure
                ,isnull(wf_1.[Risks].[HedgePortValueAsOfDate2](1,tenureMonth,quotedate,price),0) as HedgePortfolioValue
            ,[TotalOperatingGasVolume] as ActualVolume
              FROM [wf_1].[dbo].[Indices] I
            inner join
                (
                SELECT DISTINCT tenureMonth
                FROM [wf_1].[Risks].[KnowRiskTrades]
                WHERE HedgeProduct = 1
                    AND portfolio <> 'Natural Gas Hedge Transactions'
                ) B ON I.tenure=B.tenureMonth
            inner join
                (
                SELECT [Month],[TotalOperatingGasVolume]
                FROM [wf_1].[Risks].[ActualGasVolumes]
                ) C ON C.[Month]=B.tenureMonth
            WHERE HedgeProduct = 1
            AND quoteDate>=dateadd(day, -3*365, tenureMonth)
            AND quoteDate<=dateadd(day,-3,tenureMonth)
            )A 
    ) 

Answer 1

Sounds like your statistics are off.

That query is killing my eyes and I don't want to guess and pick the wrong table SO, whichever table that CI seek is hitting, update stats for at least that table and check the query again.

UPDATE STATISTICS: http://msdn.microsoft.com/en-us/library/ms187348.aspx

Answer 2

This bit raises an eyebrow, do you have an index on portfolio? Why are you using DISTINCT?

SELECT DISTINCT tenureMonth
FROM [wf_1].[Risks].[KnowRiskTrades]
WHERE HedgeProduct = 1
AND portfolio <> 'Natural Gas Hedge Transactions'

So does this:

WHERE HedgeProduct = 1
AND quoteDate>=dateadd(day, -3*365, tenureMonth)
AND quoteDate<=dateadd(day,-3,tenureMonth)

But it's hard to offer good advice without knowing where the indexes are and how much data is in the relevant tables.

Also: How long does the query take? How long do you want it to take? How often do you run it? How busy is your database server?