Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.6020.0">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementText="Update
 dbo.Code
 set
 dbo.Code.CommissioningFlag = @childrenAggregationFlag
 From 
 dbo.Code
 INNER JOIN
 @childrenIds c
 ON
 dbo.Code.CodeId = c.CodeId
 -- If an error, end here.
 " StatementId="1" StatementCompId="69" StatementType="UPDATE" RetrievedFromCache="true" StatementSubTreeCost="0.0279337" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0xCE63536A499E2308" QueryPlanHash="0xDF86DCCF203E7A0" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
- <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
- <QueryPlan CachedPlanSize="40" CompileTime="3" CompileCPU="3" CompileMemory="712">
- <MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1088" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="205707" EstimatedPagesCached="102853" EstimatedAvailableDegreeOfParallelism="4" />
- <RelOp NodeId="1" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1" EstimateIO="0.0100009" EstimateCPU="1.00009e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0279337" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList />
- <Update DMLRequestSort="0">
- <Object Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Index="[PK_CODE]" IndexKind="Clustered" />
- <Object Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Index="[ix255]" Filtered="1" IndexKind="NonClustered" />
- <SetPredicate>
- <ScalarOperator ScalarString="[TRZIC-Lv2].[dbo].[Code].[CommissioningFlag] = RaiseIfNullUpdate([@childrenAggregationFlag])">
- <ScalarExpressionList>
- <ScalarOperator>
- <MultipleAssign>
- <Assign>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CommissioningFlag" />
- <ScalarOperator>
- <Intrinsic FunctionName="RaiseIfNullUpdate">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@childrenAggregationFlag" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Assign>
- </MultipleAssign>
- </ScalarOperator>
- </ScalarExpressionList>
- </ScalarOperator>
- </SetPredicate>
- <RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0179318" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CodeId" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1011" />
- <ScalarOperator ScalarString="[Expr1011]">
- <Identifier>
- <ColumnReference Column="Expr1011" />
- </Identifier>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0179318" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CodeId" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1011" />
- <ScalarOperator ScalarString="CASE WHEN IsFalseOrNull [TRZIC-Lv2].[dbo].[Code].[CommissioningFlag]=(255) AND [@childrenAggregationFlag]=(255) THEN (4) ELSE CASE WHEN [TRZIC-Lv2].[dbo].[Code].[CommissioningFlag]=(255) AND IsFalseOrNull [@childrenAggregationFlag]=(255) THEN (3) ELSE CASE WHEN [TRZIC-Lv2].[dbo].[Code].[CommissioningFlag]=(255) AND [@childrenAggregationFlag]=(255) THEN (0) ELSE (0) END END END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Logical Operation="AND">
- <ScalarOperator>
- <Logical Operation="IsFalseOrNull">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CommissioningFlag" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(255)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@childrenAggregationFlag" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(255)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <IF>
- <Condition>
- <ScalarOperator>
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CommissioningFlag" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(255)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Logical Operation="IsFalseOrNull">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@childrenAggregationFlag" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(255)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="(3)" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <IF>
- <Condition>
- <ScalarOperator>
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CommissioningFlag" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(255)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@childrenAggregationFlag" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(255)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp NodeId="4" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100012" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0179317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CodeId" />
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CommissioningFlag" />
- </OutputList>
- <MemoryFractions Input="0.5" Output="1" />
- <Sort Distinct="1">
- <OrderBy>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CodeId" />
- </OrderByColumn>
- </OrderBy>
- <RelOp NodeId="5" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="12" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CodeId" />
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CommissioningFlag" />
- </OutputList>
- <NestedLoops Optimized="1">
- <OuterReferences>
- <ColumnReference Table="@childrenIds" Alias="[c]" Column="CodeId" />
- </OuterReferences>
- <RelOp NodeId="7" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Table="@childrenIds" Alias="[c]" Column="CodeId" />
- </OutputList>
- <TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Table="@childrenIds" Alias="[c]" Column="CodeId" />
- </DefinedValue>
- </DefinedValues>
- <Object Table="[@childrenIds]" Alias="[c]" />
- </TableScan>
- </RelOp>
- <RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1.10096e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CodeId" />
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CommissioningFlag" />
- </OutputList>
- <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CodeId" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CommissioningFlag" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Index="[PK_CODE]" IndexKind="Clustered" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[TRZIC-Lv2]" Schema="[dbo]" Table="[Code]" Column="CodeId" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="@childrenIds.[CodeId] as [c].[CodeId]">
- <Identifier>
- <ColumnReference Table="@childrenIds" Alias="[c]" Column="CodeId" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Sort>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </Update>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement