Brandon Moore
Reputation: 8780
How to properly index multiple fields for faster results
I have an index that contains ConChainNum, Control, and TransDate, and I just rebuilt the indexes. The table I'm working with has over 100 million records and the following query returns no results:
select * from sbt_pos_sales
where conchainnum = 810 and
control = 852013688 and
transdate = 20120712
Given that I have an index made specifically with the three fields in the where clause, I really expected an almost immediate result, but it took about half a minute to tell me there where no results.
Can someone explain to me why this index doesn't make it any faster than that, and if I can somehow do it differently to make it faster?
Execution Plan:
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1.1" Build="10.0.5500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="20.2265" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="348.687" StatementText="SELECT * FROM [ngdwdb]..[sbt_pos_sales] WHERE [conchainnum]=@1 AND [control]=@2 AND [transdate]=@3" StatementType="SELECT" QueryHash="0x11B117D9A8C6DF58" QueryPlanHash="0xBA3EFC04623601F6">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="8" MemoryGrant="5504" CachedPlanSize="48" CompileTime="95" CompileCPU="4" CompileMemory="368">
<RelOp AvgRowSize="193" EstimateCPU="0.0655713" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7330.87" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="348.686">
<OutputList>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="RowIdent" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ConChainNum" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Control" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="TransDate" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ControlGS" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="UPC_UCP" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="DateSold" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Price_UCP" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Store" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="QtySold" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="FileName" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="IssueCode" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="DealerNum" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Parallelism>
<RelOp AvgRowSize="193" EstimateCPU="0.00766076" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7330.87" LogicalOp="Inner Join" NodeId="2" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="348.621">
<OutputList>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="RowIdent" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ConChainNum" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Control" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="TransDate" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ControlGS" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="UPC_UCP" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="DateSold" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Price_UCP" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Store" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="QtySold" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="FileName" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="IssueCode" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="DealerNum" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="6" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<NestedLoops Optimized="true" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Column="Bmk1000" />
<ColumnReference Column="Expr1009" />
</OuterReferences>
<RelOp AvgRowSize="58" EstimateCPU="14.9196" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7330.87" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="324.579">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ConChainNum" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Control" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="TransDate" />
<ColumnReference Column="Expr1008" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="BmkToPage([Bmk1000])">
<Intrinsic FunctionName="BmkToPage">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="58" EstimateCPU="15.196" EstimateIO="294.463" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7330.87" LogicalOp="Index Seek" NodeId="6" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="309.659" TableCardinality="107302000">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ConChainNum" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Control" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="TransDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="6" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ConChainNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Control" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="TransDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Index="[idxLoad]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ConChainNum" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(810)">
<Const ConstValue="(810)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[NGDWDB].[dbo].[SBT_POS_SALES].[Control],0)=(852013688) AND CONVERT_IMPLICIT(int,[NGDWDB].[dbo].[SBT_POS_SALES].[TransDate],0)=(20120712)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Control" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(852013688)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="TransDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120712)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="136" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="7329.87" EstimateRewinds="0" EstimateRows="1" LogicalOp="RID Lookup" NodeId="11" Parallel="true" PhysicalOp="RID Lookup" EstimatedTotalSubtreeCost="24.0341" TableCardinality="107302000">
<OutputList>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="RowIdent" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ControlGS" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="UPC_UCP" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="DateSold" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Price_UCP" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Store" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="QtySold" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="FileName" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="IssueCode" />
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="DealerNum" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="6" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
<RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
<RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
<RunTimeCountersPerThread Thread="7" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
<RunTimeCountersPerThread Thread="5" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
<RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
<RunTimeCountersPerThread Thread="8" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
<RunTimeCountersPerThread Thread="1" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="RowIdent" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="ControlGS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="UPC_UCP" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="DateSold" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Price_UCP" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="Store" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="QtySold" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="FileName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="IssueCode" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" Column="DealerNum" />
</DefinedValue>
</DefinedValues>
<Object Database="[NGDWDB]" Schema="[dbo]" Table="[SBT_POS_SALES]" TableReferenceId="-1" IndexKind="Heap" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="Bmk1000" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Bmk1000]">
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
<ParameterList>
<ColumnReference Column="@3" ParameterCompiledValue="(20120712)" ParameterRuntimeValue="(20120712)" />
<ColumnReference Column="@2" ParameterCompiledValue="(852013688)" ParameterRuntimeValue="(852013688)" />
<ColumnReference Column="@1" ParameterCompiledValue="(810)" ParameterRuntimeValue="(810)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Upvotes: 1
Views: 740
Answers (1)
mwigdahl
Reputation: 16578
Rather than keep filling up the comments, I'll formalize this as an answer...
Based on your execution plan, I recommend adding a clustered index on the table. It's hard to be 100% sure what's going on, but RID lookups are a problem.
RID lookups are what happens when there's not a clustering key associated with the table. In a nonclustered index, the clustering key gets replicated in the nonclustered index if there is one, speeding up any row access required after the index entries are found. In a heap (a table with a nonclustered index) you don't have that clustering key, so you have to look it up in the heap.
That's why you have an INNER JOIN in the execution plan, even though there's no JOIN in your SQL. Applying clustering to the primary key in the table should eliminate this join, and leave it with a simple index seek.
Although the current plan shows it burning 89% of your time right now, I'll bet that with a clustered index on the table you'll simplify it down to a simple (and much faster) index seek.
EDIT:
Let me amend this after looking at your execution plan XML. Although the clustering key should still help, the real problem is the data types in your indexes. On the index seek I'm showing
CONVERT_IMPLICIT(int,[NGDWDB].[dbo].[SBT_POS_SALES].[Control],0)=(852013688) AND CONVERT_IMPLICIT(int,[NGDWDB].[dbo].[SBT_POS_SALES].[TransDate],0)=(20120712)
, which is telling me that Control and TransDate are not the same data types as you are providing in your query. If you fix this up such that no conversions are required, you will very likely see a huge speedup in the query.
Upvotes: 1
Related Questions
- Multiple indexes on a same column to improve the search speed
- SQL Server index on multiple table columns
- Index with multiple fields or multiple indexes
- Approach to index on Multiple Join columns on same Table?
- TSQL Multi Column Index
- SQL Statement - How can Improve speed with indexing
- SQL Server: multiple index for the same columns performance
- How to Index a JOIN result of two or more tables in order to improve the performance in SQL server?
- Multiple column indexes optimization for multiple column queries on SQL Server
- Individual indexes vs multiple field indexes