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
)
