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="171.439" StatementId="2" StatementOptmLevel="FULL" StatementSubTreeCost="23.1887" StatementText="
SELECT attx.intAttributeID,attx.txtAttributeName,attx.txtAttributeLabel,attx.txtType,attx.txtEntity FROM
 AST_tblAttributes attx WHERE attx.intAttributeID 
 IN
 (SELECT Distinct([TargetAttributeID]) FROM
 (SELECT 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 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)




/*
SELECT intAttributeID,txtAttributeName,txtAttributeLabel,txtType,txtEntity FROM
AST_tblAttributes att WHERE att.intAttributeID IN
 (SELECT DISTINCT att1.intAttributeID
 FROM AST_tblAttributes att1
 INNER JOIN
 AST_lnkProfileDemandAttributes pda
 ON pda.intAttributeID=att1.intAttributeID AND pda.intProfileID = @intProfileID
 UNION ALL
 SELECT DISTINCT ca2.intAttributeID FROM
 AST_lnkCapturePolicyAttributes ca2
 INNER JOIN
 AST_lnkEmployeeCapture ec2 ON ec2.intAdminCaptureID = ca2.intAdminCaptureID AND ec2.intTeamID = 57
 WHERE ec2.dteCreatedDate >= @cutoffdate)*/
" 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="39">
- <RelOp AvgRowSize="84" EstimateCPU="0.00642629" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="128.359" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="23.1887">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeName" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeLabel" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtType" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtEntity" />
- </OutputList>
- <Merge ManyToMany="false">
- <InnerSideJoinColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Column="Union1010" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_tblAttributes].[intAttributeID] as [attx].[intAttributeID]=[Union1010]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Union1010" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="15" EstimateCPU="0.00150251" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="128.359" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="23.1772">
- <OutputList>
- <ColumnReference Column="Union1010" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Column="Union1010" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="15" EstimateCPU="0.478216" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="128.359" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="23.1644">
- <OutputList>
- <ColumnReference Column="Union1010" />
- </OutputList>
- <MemoryFractions Input="0" Output="0" />
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Column="Union1010" />
- </HashKeysBuild>
- <BuildResidual>
- <ScalarOperator ScalarString="[Union1010] = [Union1010]">
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Union1010" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Union1010" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </BuildResidual>
- <RelOp AvgRowSize="15" EstimateCPU="0.0072653" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="72653" LogicalOp="Concatenation" NodeId="4" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="22.6862">
- <OutputList>
- <ColumnReference Column="Union1010" />
- </OutputList>
- <Concat>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Union1010" />
- <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.000166085" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39.7333" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0191486">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </OutputList>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </OuterReferences>
- <RelOp AvgRowSize="15" EstimateCPU="0.000200707" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39.7333" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00332571">
- <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>
- <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="38.6552" EstimateRewinds="0.0781168" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0156568">
- <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]" />
- <SeekPredicates>
- <SeekPredicate>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_lnkProfileDemandAttributes].[intAttributeID] as [pda].[intAttributeID]">
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekPredicate>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="15" EstimateCPU="0.303523" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="72613.3" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="22.6598">
- <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="Expr1011" />
- </OuterReferences>
- <RelOp AvgRowSize="23" EstimateCPU="0.183038" EstimateIO="0.642384" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9097.78" LogicalOp="Index Seek" NodeId="11" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.825422">
- <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.00016578" EstimateIO="0.003125" EstimateRebinds="9096.74" EstimateRewinds="0.042674" EstimateRows="7.98143" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="21.451">
- <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>
- </Concat>
- </RelOp>
- </Hash>
- </RelOp>
- </Sort>
- </RelOp>
- <RelOp AvgRowSize="84" EstimateCPU="0.0004419" EstimateIO="0.00460648" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="259" LogicalOp="Clustered Index Scan" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00504838">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeName" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeLabel" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtType" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtEntity" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeName" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeLabel" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtType" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtEntity" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Index="[PK_AST_tblAttributes]" Alias="[attx]" />
- </IndexScan>
- </RelOp>
- </Merge>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement