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.3" Build="13.0.201.4725" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtCond StatementCompId="1" StatementId="1" StatementText="IF (OBJECT_ID('[dbo].[GetOrganizationChart]') IS NOT NULL)" StatementType="COND" RetrievedFromCache="false">
- <Condition />
- <Then>
- <Statements>
- <StmtSimple StatementCompId="2" StatementId="2" StatementText="
DROP PROCEDURE [dbo].[GetOrganizationChart]
" StatementType="DROP OBJECT" RetrievedFromCache="false" />
- </Statements>
- </Then>
- </StmtCond>
- </Statements>
- </Batch>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="0" StatementId="1" StatementText="
-- =============================================
CREATE PROCEDURE [dbo].[GetOrganizationChart]
@identity varchar(256),
@date datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @userId varchar(256);
SET @userId = @identity;

DECLARE @endDate datetime;
SET @endDate = @date;

WITH OrganizationChart ([Identity], [DisplayName], Title, [Level], Manager) AS
(
 SELECT
 [Identity], [DisplayName], Title, 0, Manager
 FROM
 [data].[DailyUserV1] 
 WHERE 
 [Identity] = @userId AND Date = @endDate
 UNION ALL 
 SELECT
 emp.[Identity], 
 emp.[DisplayName], 
 emp.Title, 
 [Level] + 1, 
 emp.Manager
 FROM
 [data].[DailyUserV1] emp 
 INNER JOIN OrganizationChart ON emp.Manager = OrganizationChart.[Identity]
 WHERE Date = @endDate
)
SELECT * FROM OrganizationChart;
END
" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
- </Statements>
- <Statements>
- <StmtSimple StatementCompId="0" StatementId="2" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
- </Statements>
- <Statements>
- <StmtSimple StatementCompId="0" StatementId="3" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
- </Statements>
- <Statements>
- <StmtSimple StatementCompId="0" StatementId="4" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
- </Statements>
- <Statements>
- <StmtSimple StatementCompId="0" StatementId="5" StatementType="CREATE/ALTER PROCEDURE" RetrievedFromCache="false" />
- </Statements>
- </Batch>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementId="1" StatementText="
EXEC [dbo].[GetOrganizationChartV2] @identity = '7276DB4F-33B0-4074-9903-D95D740A8BF3', @date = '2015-08-03'" StatementType="EXECUTE PROC" RetrievedFromCache="false" />
- </Statements>
- <Statements>
- <StmtSimple>
- <StoredProc ProcName="dbo.GetOrganizationChartV2">
- <Statements>
- <StmtSimple StatementCompId="3" StatementId="2" StatementText="
-- =============================================
CREATE PROCEDURE [dbo].[GetOrganizationChartV2]
@identity varchar(256),
@date datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

" StatementType="SET ON/OFF" RetrievedFromCache="true" />
- <StmtSimple StatementCompId="4" StatementId="3" StatementText="DECLARE @userId varchar(256);
SET @userId = @identity;

" StatementType="ASSIGN" RetrievedFromCache="true" />
- <StmtSimple StatementCompId="5" StatementId="4" StatementText="DECLARE @endDate datetime;
SET @endDate = @date;

" StatementType="ASSIGN" RetrievedFromCache="true" />
- <StmtSimple StatementCompId="6" StatementEstRows="264.836" StatementId="5" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="125.978" StatementText="WITH OrganizationChart ([Identity], [DisplayName], Title, [Level], Manager) AS
(
 SELECT
 [Identity], [DisplayName], Title, 0, Manager
 FROM
 [data].[DailyUserV1] 
 WHERE 
 [Identity] = @userId AND Date = @endDate
 UNION ALL 
 SELECT
 emp.[Identity], 
 emp.[DisplayName], 
 emp.Title, 
 [Level] + 1, 
 emp.Manager
 FROM
 [data].[DailyUserV1] emp 
 INNER JOIN OrganizationChart ON emp.Manager = OrganizationChart.[Identity]
 WHERE Date = @endDate
)
SELECT * FROM OrganizationChart" StatementType="SELECT" QueryHash="0x4A21D320D0CECCB2" QueryPlanHash="0x873FBBD7A35C484C" RetrievedFromCache="true">
- <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
- <QueryPlan CachedPlanSize="96" CompileTime="14" CompileCPU="14" CompileMemory="1536">
- <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="2936005" EstimatedPagesCached="1101001" EstimatedAvailableDegreeOfParallelism="3" />
- <RelOp AvgRowSize="561" EstimateCPU="4.6747E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="264.836" LogicalOp="Lazy Spool" NodeId="0" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="125.978">
- <OutputList>
- <ColumnReference Column="Expr1023" />
- <ColumnReference Column="Recr1014" />
- <ColumnReference Column="Recr1015" />
- <ColumnReference Column="Recr1016" />
- <ColumnReference Column="Recr1017" />
- <ColumnReference Column="Recr1018" />
- </OutputList>
- <Spool Stack="true">
- <RelOp AvgRowSize="561" EstimateCPU="9.34939E-08" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="264.836" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="125.535">
- <OutputList>
- <ColumnReference Column="Expr1023" />
- <ColumnReference Column="Recr1014" />
- <ColumnReference Column="Recr1015" />
- <ColumnReference Column="Recr1016" />
- <ColumnReference Column="Recr1017" />
- <ColumnReference Column="Recr1018" />
- </OutputList>
- <Concat>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1023" />
- <ColumnReference Column="Expr1020" />
- <ColumnReference Column="Expr1022" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Recr1014" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Identity" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Recr1015" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="DisplayName" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Recr1016" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Title" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Title" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Recr1017" />
- <ColumnReference Column="Expr1004" />
- <ColumnReference Column="Expr1013" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Recr1018" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Manager" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Manager" />
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="561" EstimateCPU="9.34939E-07" EstimateIO="0" EstimateRebinds="264.836" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9.34939E-07">
- <OutputList>
- <ColumnReference Column="Expr1020" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Title" />
- <ColumnReference Column="Expr1004" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Manager" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1020" />
- <ScalarOperator ScalarString="(0)">
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="561" EstimateCPU="7.82278E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="7.82278" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.22817">
- <OutputList>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Manager" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Title" />
- <ColumnReference Column="Expr1004" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1004" />
- <ScalarOperator ScalarString="(0)">
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="557" EstimateCPU="3.26992E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="7.82278" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.22816">
- <OutputList>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Manager" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Title" />
- </OutputList>
- <NestedLoops Optimized="true">
- <OuterReferences>
- <ColumnReference Column="PtnId1000" ComputedColumn="true" />
- <ColumnReference Column="Uniq1002" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Date" />
- </OuterReferences>
- <RelOp AvgRowSize="34" EstimateCPU="0.0576276" EstimateIO="1.14688" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="7.82278" LogicalOp="Index Seek" NodeId="6" Parallel="false" Partitioned="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="1.2045" TableCardinality="17547700">
- <OutputList>
- <ColumnReference Column="PtnId1000" ComputedColumn="true" />
- <ColumnReference Column="Uniq1002" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Date" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Identity" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="PtnId1000" ComputedColumn="true" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Uniq1002" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Date" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Identity" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Index="[DailyUserV1_Identity]" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <StartRange ScanType="GE">
- <RangeColumns>
- <ColumnReference Column="PtnId1000" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(1)">
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- <EndRange ScanType="LE">
- <RangeColumns>
- <ColumnReference Column="PtnId1000" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(367)">
- <Const ConstValue="(367)" />
- </ScalarOperator>
- </RangeExpressions>
- </EndRange>
- </SeekKeys>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Date" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(uniqueidentifier,[@userId],0)">
- <Identifier>
- <ColumnReference Column="ConstExpr1019">
- <ScalarOperator>
- <Convert DataType="uniqueidentifier" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@userId" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- <ScalarOperator ScalarString="[@endDate]">
- <Identifier>
- <ColumnReference Column="@endDate" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="541" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="6.82278" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" Partitioned="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0236291" TableCardinality="17547700">
- <OutputList>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Manager" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Title" />
- </OutputList>
- <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Manager" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="DisplayName" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Title" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Index="[DailyUserV1_Date]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Column="PtnId1000" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Date" />
- <ColumnReference Column="Uniq1002" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[PtnId1000]">
- <Identifier>
- <ColumnReference Column="PtnId1000" ComputedColumn="true" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator ScalarString="[f686d426-8d16-42db-81b7-ab578e110ccd].[data].[DailyUserV1].[Date]">
- <Identifier>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Column="Date" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator ScalarString="[Uniq1002]">
- <Identifier>
- <ColumnReference Column="Uniq1002" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <RelOp AvgRowSize="561" EstimateCPU="7.85349E-06" EstimateIO="0" EstimateRebinds="264.836" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="33.8545" LogicalOp="Assert" NodeId="22" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="124.307">
- <OutputList>
- <ColumnReference Column="Expr1022" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Title" />
- <ColumnReference Column="Expr1013" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Manager" />
- </OutputList>
- <Assert StartupExpression="false">
- <RelOp AvgRowSize="561" EstimateCPU="7.85349E-06" EstimateIO="0" EstimateRebinds="264.836" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="33.8545" LogicalOp="Inner Join" NodeId="23" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="124.307">
- <OutputList>
- <ColumnReference Column="Expr1022" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Title" />
- <ColumnReference Column="Expr1013" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Manager" />
- </OutputList>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Column="Expr1022" />
- <ColumnReference Column="Recr1008" />
- <ColumnReference Column="Recr1009" />
- <ColumnReference Column="Recr1010" />
- <ColumnReference Column="Recr1011" />
- <ColumnReference Column="Recr1012" />
- </OuterReferences>
- <RelOp AvgRowSize="561" EstimateCPU="9.34939E-07" EstimateIO="0" EstimateRebinds="264.836" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="24" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9.34939E-07">
- <OutputList>
- <ColumnReference Column="Expr1022" />
- <ColumnReference Column="Recr1008" />
- <ColumnReference Column="Recr1009" />
- <ColumnReference Column="Recr1010" />
- <ColumnReference Column="Recr1011" />
- <ColumnReference Column="Recr1012" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1022" />
- <ScalarOperator ScalarString="[Expr1021]+(1)">
- <Arithmetic Operation="ADD">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1021" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="561" EstimateCPU="9.34939E-07" EstimateIO="0" EstimateRebinds="264.836" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="25" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="9.34939E-07">
- <OutputList>
- <ColumnReference Column="Expr1021" />
- <ColumnReference Column="Recr1008" />
- <ColumnReference Column="Recr1009" />
- <ColumnReference Column="Recr1010" />
- <ColumnReference Column="Recr1011" />
- <ColumnReference Column="Recr1012" />
- </OutputList>
- <Spool Stack="true" PrimaryNodeId="0" />
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <RelOp AvgRowSize="561" EstimateCPU="8.56711E-06" EstimateIO="0" EstimateRebinds="263.836" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="85.6711" LogicalOp="Compute Scalar" NodeId="29" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="124.307">
- <OutputList>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Manager" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Title" />
- <ColumnReference Column="Expr1013" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1013" />
- <ScalarOperator ScalarString="[Recr1011]+(1)">
- <Arithmetic Operation="ADD">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Recr1011" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="557" EstimateCPU="0.325709" EstimateIO="7.06361" EstimateRebinds="0" EstimateRewinds="263.836" EstimatedExecutionMode="Row" EstimateRows="85.6711" LogicalOp="Clustered Index Seek" NodeId="30" Parallel="false" Partitioned="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="93.3231" TableCardinality="17547700">
- <OutputList>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Identity" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Manager" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="DisplayName" />
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Title" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Identity" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Manager" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="DisplayName" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Title" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Index="[DailyUserV1_Date]" Alias="[emp]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <StartRange ScanType="GE">
- <RangeColumns>
- <ColumnReference Column="PtnId1005" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(1)">
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- <EndRange ScanType="LE">
- <RangeColumns>
- <ColumnReference Column="PtnId1005" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(367)">
- <Const ConstValue="(367)" />
- </ScalarOperator>
- </RangeExpressions>
- </EndRange>
- </SeekKeys>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Date" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[@endDate]">
- <Identifier>
- <ColumnReference Column="@endDate" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[f686d426-8d16-42db-81b7-ab578e110ccd].[data].[DailyUserV1].[Manager] as [emp].[Manager]=[Recr1008]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[f686d426-8d16-42db-81b7-ab578e110ccd]" Schema="[data]" Table="[DailyUserV1]" Alias="[emp]" Column="Manager" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Recr1008" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="CASE WHEN [Expr1022]>(100) THEN (0) ELSE NULL END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="GT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1022" />
- </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>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </StoredProc>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement