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.0" Build="9.00.4226.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="2" StatementEstRows="129.217" StatementId="2" StatementOptmLevel="FULL" StatementSubTreeCost="23.8901" StatementText="
SELECT Distinct([TargetAttributeID]) FROM
 (SELECT distinct att1.intAttributeID as [TargetAttributeID]
 FROM AST_tblAttributes att1
 INNER JOIN
 AST_lnkProfileDemandAttributes pda
 ON pda.intAttributeID=att1.intAttributeID AND pda.intProfileID = @intProfileID

 union all

 SELECT distinct ca2.intAttributeID as [TargetAttributeID] FROM
 AST_lnkCapturePolicyAttributes ca2
 INNER JOIN
 AST_lnkEmployeeCapture ec2 ON ec2.intAdminCaptureID = ca2.intAdminCaptureID AND ec2.intTeamID = 57
 WHERE ec2.dteCreatedDate >= @cutoffdate) x" 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 CachedPlanSize="32">
- <RelOp AvgRowSize="15" EstimateCPU="0.00160658" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="129.217" LogicalOp="Distinct Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="23.8901">
- <OutputList>
- <ColumnReference Column="Union1008" />
- </OutputList>
- <MemoryFractions Input="0.00554017" Output="1" />
- <Sort Distinct="true">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Column="Union1008" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="15" EstimateCPU="1.36217E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="136.217" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="23.8773">
- <OutputList>
- <ColumnReference Column="Union1008" />
- </OutputList>
- <Concat>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Union1008" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="15" EstimateCPU="0.00623737" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="38.5776" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.0263472">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </OutputList>
- <Merge ManyToMany="false">
- <InnerSideJoinColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_lnkProfileDemandAttributes].[intAttributeID] as [pda].[intAttributeID]=[CCMI].[dbo].[AST_tblAttributes].[intAttributeID] as [att1].[intAttributeID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="15" EstimateCPU="0.000443" EstimateIO="0.00460648" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="260" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00504948">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Index="[PK_AST_tblAttributes]" Alias="[att1]" />
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="15" EstimateCPU="0.000466532" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="41.6747" LogicalOp="Distinct Sort" NodeId="5" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0150574">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </OutputList>
- <MemoryFractions Input="0.99446" Output="0.99446" />
- <Sort Distinct="true">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="15" EstimateCPU="0.000204559" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43.2353" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00332956">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Index="[PK_AST_lnkProfileDemandAttributes]" Alias="[pda]" />
- <SeekPredicates>
- <SeekPredicate>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intProfileID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[@intProfileID]">
- <Identifier>
- <ColumnReference Column="@intProfileID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekPredicate>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </Sort>
- </RelOp>
- </Merge>
- </RelOp>
- <RelOp AvgRowSize="15" EstimateCPU="0.49344" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="113.143" LogicalOp="Aggregate" NodeId="9" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="23.8509">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </OutputList>
- <MemoryFractions Input="0" Output="0" />
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </HashKeysBuild>
- <BuildResidual>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_lnkCapturePolicyAttributes].[intAttributeID] as [ca2].[intAttributeID] = [CCMI].[dbo].[AST_lnkCapturePolicyAttributes].[intAttributeID] as [ca2].[intAttributeID]">
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </BuildResidual>
- <RelOp AvgRowSize="15" EstimateCPU="0.314135" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="75152" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="23.3575">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </OutputList>
- <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intAdminCaptureID" />
- <ColumnReference Column="Expr1009" />
- </OuterReferences>
- <RelOp AvgRowSize="23" EstimateCPU="0.189439" EstimateIO="0.665347" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9416.25" LogicalOp="Index Seek" NodeId="13" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.854787">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intTeamID" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intAdminCaptureID" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intTeamID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intAdminCaptureID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Index="[idxAST_lnkEmployeeCapturedteCreatedDate]" Alias="[ec2]" />
- <SeekPredicates>
- <SeekPredicate>
- <StartRange ScanType="GE">
- <RangeColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="dteCreatedDate" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[@cutoffdate]">
- <Identifier>
- <ColumnReference Column="@cutoffdate" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- </SeekPredicate>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_lnkEmployeeCapture].[intTeamID] as [ec2].[intTeamID]=(57)">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intTeamID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(57)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="15" EstimateCPU="0.000165779" EstimateIO="0.003125" EstimateRebinds="9415.2" EstimateRewinds="0.042674" EstimateRows="7.9811" LogicalOp="Clustered Index Seek" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="22.106">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Index="[PK_AST_lnkCapturePolicyAttributes]" Alias="[ca2]" />
- <SeekPredicates>
- <SeekPredicate>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAdminCaptureID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_lnkEmployeeCapture].[intAdminCaptureID] as [ec2].[intAdminCaptureID]">
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intAdminCaptureID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekPredicate>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Hash>
- </RelOp>
- </Concat>
- </RelOp>
- </Sort>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement