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.481" Build="14.0.3391.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="140" StatementSubTreeCost="3.13882" StatementText="SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) - ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0) 
 FROM [Credit] M WITH(NOLOCK) INNER JOIN [Transaction] T4 WITH(NOLOCK) ON M.f_accounts=T4.f_accounts
 AND 
 (
 T4.f_time>=M.f_time OR M.f_time IS NULL
 )
 AND T4.f_other IS NULL 
 AND (T4.f_in=12 OR T4.f_out=12)" StatementType="SELECT" QueryHash="0xDD399CF54720BCBF" QueryPlanHash="0xCD2465F4AACEB1A0" RetrievedFromCache="false" SecurityPolicyApplied="false">
- <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" MemoryGrant="1024" CachedPlanSize="72" CompileTime="5" CompileCPU="5" CompileMemory="832">
- <MissingIndexes>
- <MissingIndexGroup Impact="52.9214">
- <MissingIndex Database="[web_test]" Schema="[dbo]" Table="[Transaction]">
- <ColumnGroup Usage="EQUALITY">
- <Column Name="[f_other]" ColumnId="11" />
- </ColumnGroup>
- <ColumnGroup Usage="INCLUDE">
- <Column Name="[f_accounts]" ColumnId="2" />
- <Column Name="[f_time]" ColumnId="3" />
- <Column Name="[f_money]" ColumnId="4" />
- <Column Name="[f_in]" ColumnId="12" />
- <Column Name="[f_out]" ColumnId="13" />
- </ColumnGroup>
- </MissingIndex>
- </MissingIndexGroup>
- </MissingIndexes>
- <Warnings>
- <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16" />
- </Warnings>
- <MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" RequiredMemory="16" DesiredMemory="24" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="10792248" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="167769" EstimatedPagesCached="419423" EstimatedAvailableDegreeOfParallelism="20" MaxCompileMemory="35197328" />
- <OptimizerStatsUsage>
- <StatisticsInfo Database="[web_test]" Schema="[dbo]" Table="[Credit]" Statistics="[_WA_Sys_00000002_4460231C]" ModificationCount="0" SamplingPercent="100" LastUpdate="2021-07-21T12:47:38.73" />
- <StatisticsInfo Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Statistics="[_WA_Sys_00000002_220B0B18]" ModificationCount="0" SamplingPercent="73.9774" LastUpdate="2021-07-21T09:41:09.8" />
- <StatisticsInfo Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Statistics="[_WA_Sys_0000000B_220B0B18]" ModificationCount="0" SamplingPercent="73.9774" LastUpdate="2021-07-21T09:41:09.1" />
- <StatisticsInfo Database="[web_test]" Schema="[dbo]" Table="[Credit]" Statistics="[IX_Credit_f_time]" ModificationCount="0" SamplingPercent="100" LastUpdate="2021-07-21T12:47:38.76" />
- <StatisticsInfo Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Statistics="[_WA_Sys_0000000C_220B0B18]" ModificationCount="0" SamplingPercent="73.9774" LastUpdate="2021-07-21T12:02:58.47" />
- <StatisticsInfo Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Statistics="[_WA_Sys_0000000D_220B0B18]" ModificationCount="0" SamplingPercent="73.9774" LastUpdate="2021-07-21T12:02:58.83" />
- <StatisticsInfo Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Statistics="[_WA_Sys_00000003_220B0B18]" ModificationCount="0" SamplingPercent="73.9774" LastUpdate="2021-07-21T09:41:09.93" />
- </OptimizerStatsUsage>
- <WaitStats>
- <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="1" WaitCount="800" />
- <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="495" WaitCount="3045" />
- </WaitStats>
- <QueryTimeStats CpuTime="12223" ElapsedTime="12712" />
- <RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="3.13882">
- <OutputList>
- <ColumnReference Column="Expr1004" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1004" />
- <ScalarOperator ScalarString="isnull([Expr1002],(0.0000000000000000e+000))-isnull([Expr1003],(0.0000000000000000e+000))">
- <Arithmetic Operation="SUB">
- <ScalarOperator>
- <Intrinsic FunctionName="isnull">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1002" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0.0000000000000000e+000)" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- <ScalarOperator>
- <Intrinsic FunctionName="isnull">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1003" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0.0000000000000000e+000)" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="23" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="3.13882">
- <OutputList>
- <ColumnReference Column="Expr1002" />
- <ColumnReference Column="Expr1003" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1002" />
- <ScalarOperator ScalarString="CASE WHEN [Expr1027]=(0) THEN NULL ELSE [Expr1028] END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1027" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1028" />
- </Identifier>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1003" />
- <ScalarOperator ScalarString="CASE WHEN [Expr1029]=(0) THEN NULL ELSE [Expr1030] END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1029" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1030" />
- </Identifier>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="23" EstimateCPU="0.000387299" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="3.13882">
- <OutputList>
- <ColumnReference Column="Expr1027" />
- <ColumnReference Column="Expr1028" />
- <ColumnReference Column="Expr1029" />
- <ColumnReference Column="Expr1030" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="12712" ActualCPUms="12222" />
- </RunTimeInformation>
- <StreamAggregate>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1027" />
- <ScalarOperator ScalarString="COUNT_BIG([Expr1005])">
- <Aggregate AggType="COUNT_BIG" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1005" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1028" />
- <ScalarOperator ScalarString="SUM([Expr1005])">
- <Aggregate AggType="SUM" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1005" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1029" />
- <ScalarOperator ScalarString="COUNT_BIG([Expr1006])">
- <Aggregate AggType="COUNT_BIG" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1006" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1030" />
- <ScalarOperator ScalarString="SUM([Expr1006])">
- <Aggregate AggType="SUM" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1006" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="23" EstimateCPU="0.0026947" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="644.665" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3.13844">
- <OutputList>
- <ColumnReference Column="Expr1005" />
- <ColumnReference Column="Expr1006" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="527" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="12708" ActualCPUms="12218" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_accounts" />
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_time" />
- </OuterReferences>
- <RelOp AvgRowSize="66" EstimateCPU="0.0227087" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="644.665" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.38616">
- <OutputList>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_accounts" />
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_time" />
- <ColumnReference Column="Expr1005" />
- <ColumnReference Column="Expr1006" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1005" />
- <ScalarOperator ScalarString="CASE WHEN [web_test].[dbo].[Transaction].[f_in] as [T4].[f_in]=(1.2000000000000000e+001) THEN [web_test].[dbo].[Transaction].[f_money] as [T4].[f_money] ELSE NULL END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_in" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1.2000000000000000e+001)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_money" />
- </Identifier>
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1006" />
- <ScalarOperator ScalarString="CASE WHEN [web_test].[dbo].[Transaction].[f_out] as [T4].[f_out]=(1.2000000000000000e+001) THEN [web_test].[dbo].[Transaction].[f_money] as [T4].[f_money] ELSE NULL END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_out" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1.2000000000000000e+001)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_money" />
- </Identifier>
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="58" EstimateCPU="0.249953" EstimateIO="1.1135" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="644.665" EstimatedRowsRead="227087" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.36345" TableCardinality="227087">
- <OutputList>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_accounts" />
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_time" />
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_money" />
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_in" />
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_out" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="780" ActualRowsRead="227087" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="53" ActualCPUms="53" ActualScans="1" ActualLogicalReads="1507" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_accounts" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_time" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_money" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_in" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_out" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Index="[PK_Transaction]" Alias="[T4]" IndexKind="Clustered" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[web_test].[dbo].[Transaction].[f_other] as [T4].[f_other] IS NULL AND ([web_test].[dbo].[Transaction].[f_in] as [T4].[f_in]=(1.2000000000000000e+001) OR [web_test].[dbo].[Transaction].[f_out] as [T4].[f_out]=(1.2000000000000000e+001))">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_other" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Logical Operation="OR">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_in" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1.2000000000000000e+001)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_out" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1.2000000000000000e+001)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <RelOp AvgRowSize="19" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="643.665" EstimateRewinds="4.03645E-07" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.4586" TableCardinality="141244">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="527" Batches="0" ActualEndOfScans="780" ActualExecutions="780" ActualExecutionMode="Row" ActualElapsedms="12652" ActualCPUms="12162" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Column="Expr1021" />
- <ColumnReference Column="Expr1022" />
- <ColumnReference Column="Expr1023" />
- </OuterReferences>
- <RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Merge Interval" NodeId="7" Parallel="false" PhysicalOp="Merge Interval" EstimatedTotalSubtreeCost="0">
- <OutputList>
- <ColumnReference Column="Expr1021" />
- <ColumnReference Column="Expr1022" />
- <ColumnReference Column="Expr1023" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1560" Batches="0" ActualEndOfScans="780" ActualExecutions="780" ActualExecutionMode="Row" ActualElapsedms="33" ActualCPUms="33" />
- </RunTimeInformation>
- <MergeInterval>
- <RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="TopN Sort" NodeId="8" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0">
- <OutputList>
- <ColumnReference Column="Expr1021" />
- <ColumnReference Column="Expr1022" />
- <ColumnReference Column="Expr1023" />
- <ColumnReference Column="Expr1024" />
- <ColumnReference Column="Expr1025" />
- <ColumnReference Column="Expr1026" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="780" ActualRewinds="0" ActualRows="1560" Batches="0" ActualEndOfScans="780" ActualExecutions="780" ActualExecutionMode="Row" ActualElapsedms="32" ActualCPUms="32" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="1024" UsedMemoryGrant="16" />
- </RunTimeInformation>
- <TopSort Distinct="false" Rows="2">
- <OrderBy>
- <OrderByColumn Ascending="false">
- <ColumnReference Column="Expr1024" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Column="Expr1025" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Column="Expr1021" />
- </OrderByColumn>
- <OrderByColumn Ascending="false">
- <ColumnReference Column="Expr1026" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Compute Scalar" NodeId="9" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0">
- <OutputList>
- <ColumnReference Column="Expr1021" />
- <ColumnReference Column="Expr1022" />
- <ColumnReference Column="Expr1023" />
- <ColumnReference Column="Expr1024" />
- <ColumnReference Column="Expr1025" />
- <ColumnReference Column="Expr1026" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1024" />
- <ScalarOperator ScalarString="((4)&[Expr1023]) = (4) AND NULL = [Expr1021]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Arithmetic Operation="BIT_AND">
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1023" />
- </Identifier>
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1021" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1025" />
- <ScalarOperator ScalarString="(4)&[Expr1023]">
- <Arithmetic Operation="BIT_AND">
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1023" />
- </Identifier>
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1026" />
- <ScalarOperator ScalarString="(16)&[Expr1023]">
- <Arithmetic Operation="BIT_AND">
- <ScalarOperator>
- <Const ConstValue="(16)" />
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1023" />
- </Identifier>
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Concatenation" NodeId="10" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0">
- <OutputList>
- <ColumnReference Column="Expr1021" />
- <ColumnReference Column="Expr1022" />
- <ColumnReference Column="Expr1023" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1560" Batches="0" ActualEndOfScans="780" ActualExecutions="780" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="1" />
- </RunTimeInformation>
- <Concat>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1021" />
- <ColumnReference Column="Expr1016" />
- <ColumnReference Column="Expr1019" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1022" />
- <ColumnReference Column="Expr1017" />
- <ColumnReference Column="Expr1020" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1023" />
- <ColumnReference Column="Expr1015" />
- <ColumnReference Column="Expr1018" />
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="11" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0">
- <OutputList>
- <ColumnReference Column="Expr1016" />
- <ColumnReference Column="Expr1017" />
- <ColumnReference Column="Expr1015" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1016" />
- <ScalarOperator ScalarString="NULL">
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1017" />
- <ScalarOperator ScalarString="[web_test].[dbo].[Transaction].[f_time] as [T4].[f_time]">
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_time" />
- </Identifier>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1015" />
- <ScalarOperator ScalarString="(42)">
- <Const ConstValue="(42)" />
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="0" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="12" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="0">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="780" Batches="0" ActualEndOfScans="780" ActualExecutions="780" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
- </RunTimeInformation>
- <ConstantScan />
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="13" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0">
- <OutputList>
- <ColumnReference Column="Expr1019" />
- <ColumnReference Column="Expr1020" />
- <ColumnReference Column="Expr1018" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1019" />
- <ScalarOperator ScalarString="NULL">
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1020" />
- <ScalarOperator ScalarString="NULL">
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1018" />
- <ScalarOperator ScalarString="(60)">
- <Const ConstValue="(60)" />
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="0" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="14" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="0">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="780" Batches="0" ActualEndOfScans="780" ActualExecutions="780" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
- </RunTimeInformation>
- <ConstantScan />
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </Concat>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </TopSort>
- </RelOp>
- </MergeInterval>
- </RelOp>
- <RelOp AvgRowSize="19" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="643.665" EstimateRewinds="4.03645E-07" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="15" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="1.4586" TableCardinality="141244">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="527" ActualRowsRead="89290306" Batches="0" ActualEndOfScans="1560" ActualExecutions="1560" ActualExecutionMode="Row" ActualElapsedms="12617" ActualCPUms="12127" ActualScans="1560" ActualLogicalReads="485643" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues />
- <Object Database="[web_test]" Schema="[dbo]" Table="[Credit]" Index="[IX_Credit_f_time]" Alias="[M]" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <StartRange ScanType="GT">
- <RangeColumns>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Credit]" Alias="[M]" Column="f_time" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[Expr1021]">
- <Identifier>
- <ColumnReference Column="Expr1021" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- <EndRange ScanType="LT">
- <RangeColumns>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Credit]" Alias="[M]" Column="f_time" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[Expr1022]">
- <Identifier>
- <ColumnReference Column="Expr1022" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </EndRange>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[web_test].[dbo].[Credit].[f_accounts] as [M].[f_accounts]=[web_test].[dbo].[Transaction].[f_accounts] as [T4].[f_accounts]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Credit]" Alias="[M]" Column="f_accounts" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[web_test]" Schema="[dbo]" Table="[Transaction]" Alias="[T4]" Column="f_accounts" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </StreamAggregate>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement