Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?xml version="1.0" encoding="utf-16"?>
- <ShowPlanXML xmlns:Asi="http://www.w3.org/2001/XMLSchema-instance" xmlns:Xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.4035.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="3" StatementEstRows="95.7588" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1.5309" StatementTeAt="SELECT DISTINCT
 [Data].ID AS [ID]
FROM 
 dbo.[Data] AS [Data]
JOIN
 dbo.[Data] AS [Compared] ON
 [Data].[A] = [Compared].[A] AND
 [Data].[B] = [Compared].[B] AND
 [Data].[C] = [Compared].[C] AND
 [Data].[G] = [Compared].[G] AND
 ([Data].[D] = [Compared].[D] OR
 [Data].[E] = [Compared].[E]) AND
 [Data].[F] <> [Compared].[F]
WHERE 1=1
 AND [Data].A = @A
 AND @CS <= [Data].C
 AND [Data].C < @CE" StatementType="SELECT">
- <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
- <QueryPlan DegreeOfParallelism="1" MemoryGrant="64" CachedPlanSize="28" CompileTime="20" CompileCPU="20" CompileMemory="608">
- <RelOp AvgRowSize="15" EstimateCPU="0.00108316" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="95.7588" LogicalOp="Distinct Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.5309">
- <OutputList>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="ID" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="299" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="true">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="ID" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="15" EstimateCPU="0.0424191" EstimateIO="0.000939" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="95.7588" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="1.51856">
- <OutputList>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="ID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="370" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Merge ManyToMany="true">
- <InnerSideJoinColumns>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="B" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="C" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="G" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="B" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="C" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="G" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[DB].[dbo].[Data].[B] as [Data].[B]=[DB].[dbo].[Data].[B] as [Compared].[B] AND [DB].[dbo].[Data].[C] as [Data].[C]=[DB].[dbo].[Data].[C] as [Compared].[C] AND [DB].[dbo].[Data].[G] as [Data].[G]=[DB].[dbo].[Data].[G] as [Compared].[G] AND ([DB].[dbo].[Data].[D] as [Data].[D]=[DB].[dbo].[Data].[D] as [Compared].[D] OR [DB].[dbo].[Data].[E] as [Data].[E]=[DB].[dbo].[Data].[E] as [Compared].[E]) AND [DB].[dbo].[Data].[F] as [Data].[F]<>[DB].[dbo].[Data].[F] as [Compared].[F]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="B" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="B" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="C" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="C" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="G" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="G" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Logical Operation="OR">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="D" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="D" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="E" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="E" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="NE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="F" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="F" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="51" EstimateCPU="0.0836881" EstimateIO="0.587076" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6834.36" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.670764">
- <OutputList>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="B" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="C" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="D" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="E" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="G" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="F" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="21469" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoEApandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="B" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="C" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="D" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="E" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="G" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="F" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[DB]" Schema="[dbo]" Table="[Data]" Index="[IDA_Data_A_B_C_G_D_E_F]" Alias="[Compared]" />
- <SeekPredicates>
- <SeekPredicate>
- <PrefiA ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="A" />
- </RangeColumns>
- <RangeEApressions>
- <ScalarOperator ScalarString="[@A]">
- <Identifier>
- <ColumnReference Column="@A" />
- </Identifier>
- </ScalarOperator>
- </RangeEApressions>
- </PrefiA>
- </SeekPredicate>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[DB].[dbo].[Data].[C] as [Compared].[C]>=[@CS] AND [DB].[dbo].[Data].[C] as [Compared].[C]<[@CE]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="GE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="C" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@CS" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="LT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Compared]" Column="C" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@CE" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="59" EstimateCPU="0.0836881" EstimateIO="0.587076" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6834.36" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.670764">
- <OutputList>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="ID" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="B" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="C" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="D" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="E" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="G" />
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="F" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="21469" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoEApandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="ID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="B" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="C" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="D" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="E" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="G" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="F" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[DB]" Schema="[dbo]" Table="[Data]" Index="[IDA_Data_A_B_C_G_D_E_F]" Alias="[Data]" />
- <SeekPredicates>
- <SeekPredicate>
- <PrefiA ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="A" />
- </RangeColumns>
- <RangeEApressions>
- <ScalarOperator ScalarString="[@A]">
- <Identifier>
- <ColumnReference Column="@A" />
- </Identifier>
- </ScalarOperator>
- </RangeEApressions>
- </PrefiA>
- </SeekPredicate>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[@CS]<=[DB].[dbo].[Data].[C] as [Data].[C] AND [DB].[dbo].[Data].[C] as [Data].[C]<[@CE]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="LE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@CS" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="C" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="LT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[DB]" Schema="[dbo]" Table="[Data]" Alias="[Data]" Column="C" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@CE" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Merge>
- </RelOp>
- </Sort>
- </RelOp>
- <ParameterList>
- <ColumnReference Column="@CE" ParameterRuntimeValue="'2010-06-16 22:45:39.700'" />
- <ColumnReference Column="@CS" ParameterRuntimeValue="'2010-04-01 00:00:00.000'" />
- <ColumnReference Column="@A" ParameterRuntimeValue="N'123'" />
- </ParameterList>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement