Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="364882" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="908.634" StatementText="with r(entity_key, parent_key, level) as
(
 select entity_key, parent_key, 1
 from #linkage
 where entity_key = parent_key
 union all
 select p.entity_key, r.parent_key, r.level + 1
 from #linkage p
 inner join r on p.parent_key = r.entity_key 
 where p.entity_key <> r.entity_key
)
select entity_key, parent_key as ultimate_parent_key, level
from r
where r.level > 2" StatementType="SELECT" QueryHash="0x9E5E60CF3C264287" QueryPlanHash="0x7F8E15BF31EAC40D">
- <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="1" CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="304">
- <RelOp AvgRowSize="27" EstimateCPU="0.583812" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="364882" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="908.634">
- <OutputList>
- <ColumnReference Column="Recr1012" />
- <ColumnReference Column="Recr1013" />
- <ColumnReference Column="Recr1014" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Filter StartupExpression="false">
- <RelOp AvgRowSize="27" EstimateCPU="0.00608137" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1216270" LogicalOp="Lazy Spool" NodeId="1" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="908.05">
- <OutputList>
- <ColumnReference Column="Expr1018" />
- <ColumnReference Column="Recr1012" />
- <ColumnReference Column="Recr1013" />
- <ColumnReference Column="Recr1014" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="12162738" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Spool Stack="true">
- <RelOp AvgRowSize="27" EstimateCPU="0.00121627" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1216270" LogicalOp="Concatenation" NodeId="2" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="908.044">
- <OutputList>
- <ColumnReference Column="Expr1018" />
- <ColumnReference Column="Recr1012" />
- <ColumnReference Column="Recr1013" />
- <ColumnReference Column="Recr1014" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="12162738" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Concat>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1018" />
- <ColumnReference Column="Expr1015" />
- <ColumnReference Column="Expr1017" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Recr1012" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="entity_key" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Recr1013" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="parent_key" />
- <ColumnReference Column="Recr1009" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Recr1014" />
- <ColumnReference Column="Expr1004" />
- <ColumnReference Column="Expr1011" />
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="27" EstimateCPU="0.0121627" EstimateIO="0" EstimateRebinds="1216270" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0121627">
- <OutputList>
- <ColumnReference Column="Expr1015" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="entity_key" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="parent_key" />
- <ColumnReference Column="Expr1004" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1015" />
- <ScalarOperator ScalarString="(0)">
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="27" EstimateCPU="0.121627" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1216270" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="62.3413">
- <OutputList>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="entity_key" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="parent_key" />
- <ColumnReference Column="Expr1004" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1004" />
- <ScalarOperator ScalarString="(1)">
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="23" EstimateCPU="13.3792" EstimateIO="43.0024" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1216270" LogicalOp="Table Scan" NodeId="5" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="56.3816" TableCardinality="12162700">
- <OutputList>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="entity_key" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="parent_key" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="11149533" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="entity_key" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="parent_key" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" IndexKind="Heap" />
- <Predicate>
- <ScalarOperator ScalarString="[tempdb].[dbo].[#linkage].[entity_key]=[tempdb].[dbo].[#linkage].[parent_key]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="entity_key" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Column="parent_key" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </TableScan>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <RelOp AvgRowSize="27" EstimateCPU="0.102167" EstimateIO="0" EstimateRebinds="1216270" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="12" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="845.701">
- <OutputList>
- <ColumnReference Column="Expr1017" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- <ColumnReference Column="Recr1009" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1013205" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Assert StartupExpression="false">
- <RelOp AvgRowSize="27" EstimateCPU="0.102167" EstimateIO="0" EstimateRebinds="1216270" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="13" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="845.701">
- <OutputList>
- <ColumnReference Column="Expr1017" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- <ColumnReference Column="Recr1009" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1013205" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Column="Expr1017" />
- <ColumnReference Column="Recr1008" />
- <ColumnReference Column="Recr1009" />
- <ColumnReference Column="Recr1010" />
- </OuterReferences>
- <RelOp AvgRowSize="27" EstimateCPU="0.0121627" EstimateIO="0" EstimateRebinds="1216270" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="14" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0121627">
- <OutputList>
- <ColumnReference Column="Expr1017" />
- <ColumnReference Column="Recr1008" />
- <ColumnReference Column="Recr1009" />
- <ColumnReference Column="Recr1010" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1017" />
- <ScalarOperator ScalarString="[Expr1016]+(1)">
- <Arithmetic Operation="ADD">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1016" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="27" EstimateCPU="0.0121627" EstimateIO="0" EstimateRebinds="1216270" EstimateRewinds="0" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="15" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0121627">
- <OutputList>
- <ColumnReference Column="Expr1016" />
- <ColumnReference Column="Recr1008" />
- <ColumnReference Column="Recr1009" />
- <ColumnReference Column="Recr1010" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="12162738" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Spool Stack="true" PrimaryNodeId="1" />
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <RelOp AvgRowSize="19" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="1216270" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="19" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="845.587">
- <OutputList>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1011" />
- <ScalarOperator ScalarString="[Recr1010]+(1)">
- <Arithmetic Operation="ADD">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Recr1010" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="15" EstimateCPU="5.16195E-07" EstimateIO="0" EstimateRebinds="1216270" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="20" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="845.465">
- <OutputList>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1013205" ActualEndOfScans="12162738" ActualExecutions="12162738" />
- </RunTimeInformation>
- <Filter StartupExpression="false">
- <RelOp AvgRowSize="15" EstimateCPU="12.163" EstimateIO="316.087" EstimateRebinds="1216270" EstimateRewinds="0" EstimateRows="1.07541" LogicalOp="Eager Spool" NodeId="21" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="844.838">
- <OutputList>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="12162738" ActualRewinds="0" ActualRows="12162738" ActualEndOfScans="12162738" ActualExecutions="12162738" />
- </RunTimeInformation>
- <Spool>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="parent_key" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[Recr1008]">
- <Identifier>
- <ColumnReference Column="Recr1008" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- <RelOp AvgRowSize="23" EstimateCPU="13.3792" EstimateIO="43.0024" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="12162700" LogicalOp="Table Scan" NodeId="22" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="56.3816" TableCardinality="12162700">
- <OutputList>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="parent_key" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="12162738" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="parent_key" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" IndexKind="Heap" />
- </TableScan>
- </RelOp>
- </Spool>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[tempdb].[dbo].[#linkage].[entity_key] as [p].[entity_key]<>[Recr1008]">
- <Compare CompareOp="NE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#linkage]" Alias="[p]" Column="entity_key" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Recr1008" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="CASE WHEN [Expr1017]>(100) THEN (0) ELSE NULL END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="GT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1017" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(100)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </Predicate>
- </Assert>
- </RelOp>
- </Concat>
- </RelOp>
- </Spool>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[Recr1014]>(2)">
- <Compare CompareOp="GT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Recr1014" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(2)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement