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.1" Build="12.0.5000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0132873" StatementText="insert into @associatedAccountsTable
select * from [dbo].[Split](@associatedAccounts, ',')" StatementType="INSERT" QueryHash="0xD489D9126FC5A636" QueryPlanHash="0x50F8846BA49AF2B9" 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 DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="504">
- <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="208659" EstimatedPagesCached="104329" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="8443160" />
- <RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="0.0132873">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" />
- </RunTimeInformation>
- <Sequence>
- <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table-valued function" NodeId="1" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <TableValuedFunction>
- <DefinedValues />
- <Object Database="[dbname]" Schema="[dbo]" Table="[Split]" Storage="RowStore" />
- <ParameterList>
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(2000),[@associatedAccounts],0)">
- <Identifier>
- <ColumnReference Column="ConstExpr1006">
- <ScalarOperator>
- <Convert DataType="nvarchar" Length="4000" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@associatedAccounts" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- <ScalarOperator ScalarString="N','">
- <Const ConstValue="N','" />
- </ScalarOperator>
- </ParameterList>
- </TableValuedFunction>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Insert" NodeId="4" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="0.0132841">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <Update DMLRequestSort="false">
- <Object Table="[@associatedAccountsTable]" Storage="RowStore" />
- <SetPredicate>
- <ScalarOperator ScalarString="[id] = [dbname].[dbo].[Split].[Id],[data] = [dbname].[dbo].[Split].[Data]">
- <ScalarExpressionList>
- <ScalarOperator>
- <MultipleAssign>
- <Assign>
- <ColumnReference Column="id" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Split]" Column="Id" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- <Assign>
- <ColumnReference Column="data" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Split]" Column="Data" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- </MultipleAssign>
- </ScalarOperator>
- </ScalarExpressionList>
- </ScalarOperator>
- </SetPredicate>
- <RelOp AvgRowSize="115" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="5" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Split]" Column="Id" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Split]" Column="Data" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="4" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Split]" Column="Id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Split]" Column="Data" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[Split]" Storage="RowStore" />
- </TableScan>
- </RelOp>
- </Update>
- </RelOp>
- </Sequence>
- </RelOp>
- <ParameterList>
- <ColumnReference Column="@associatedAccounts" ParameterCompiledValue="N'001C00000157N6BIAU,7e38acfb-df15-461b-86de-436385ef409f,c9187212-8276-40ad-9262-9ade65dfcf42,e9f2f9b1-b12a-4563-9188-430b17e7bda4'" ParameterRuntimeValue="N'001C00000157N6BIAU,7e38acfb-df15-461b-86de-436385ef409f,c9187212-8276-40ad-9262-9ade65dfcf42,e9f2f9b1-b12a-4563-9188-430b17e7bda4'" />
- </ParameterList>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="7" StatementEstRows="58238.2" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="33.8486" StatementText="select * from (
select ri.ReaderSerialNumber as Value,
case 
 when ri.readertitle is null or len(ri.readertitle) = 0 then ri.ReaderSerialNumber
 else ri.readertitle
end as Name
from ReaderInfo ri
inner join Asset ass on ri.ReaderSerialNumber = ass.SerialNumber
where ass.AccountNumber = @accountId
union
select ri.ReaderSerialNumber as Value,
case 
 when ri.readertitle is null or len(ri.readertitle) = 0 then ri.ReaderSerialNumber
 else ri.readertitle
end as Name
from ReaderInfo ri
inner join MastitisTest mt on ri.ReaderSerialNumber = mt.ReaderSerialNumber
inner join Account a on a.AccountNumber = mt.AccountNumber
inner join CustomerLocation cl on mt.CustomerLocationID = cl.ID
left join Account ca on ca.AccountNumber = cl.ConsumingAccountNumber
where (a.AccountNumber = @accountId and (@country is null or a.Country = @country)) or
(cl.ConsumingAccountNumber in (select data from @associatedAccountsTable) and (@country is null or ca.Country = @country)))
as Readers" StatementType="SELECT" QueryHash="0x6B0EE8F272703286" QueryPlanHash="0xE7B49E44CC903FD9" 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 DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="80" CompileTime="63" CompileCPU="63" CompileMemory="2576">
- <Warnings>
- <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="24" />
- </Warnings>
- <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="560" RequiredMemory="512" DesiredMemory="560" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="24" MaxQueryMemory="2018712" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="208659" EstimatedPagesCached="104329" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="8443160" />
- <RelOp AvgRowSize="4067" EstimateCPU="0.133728" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58238.2" LogicalOp="Union" NodeId="0" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="33.8486">
- <OutputList>
- <ColumnReference Column="Union1012" />
- <ColumnReference Column="Union1013" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="152" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="417" ActualCPUms="417" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <Merge>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Union1012" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Union1013" />
- <ColumnReference Column="Expr1002" />
- <ColumnReference Column="Expr1011" />
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="4091" EstimateCPU="0.0063838" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="114.608" LogicalOp="Left Semi Join" NodeId="2" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.0360788">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Column="Expr1002" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="133" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <Merge ManyToMany="false">
- <InnerSideJoinColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Asset]" Alias="[ass]" Column="SerialNumber" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[dbname].[dbo].[Asset].[SerialNumber] as [ass].[SerialNumber]=[dbname].[dbo].[ReaderInfo].[ReaderSerialNumber] as [ri].[ReaderSerialNumber]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Asset]" Alias="[ass]" Column="SerialNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="4091" EstimateCPU="2.29E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="229" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00948273">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Column="Expr1002" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1002" />
- <ScalarOperator ScalarString="CASE WHEN [dbname].[dbo].[ReaderInfo].[ReaderTitle] as [ri].[ReaderTitle] IS NULL OR len([dbname].[dbo].[ReaderInfo].[ReaderTitle] as [ri].[ReaderTitle])=(0) THEN CONVERT_IMPLICIT(nvarchar(max),[dbname].[dbo].[ReaderInfo].[ReaderSerialNumber] as [ri].[ReaderSerialNumber],0) ELSE [dbname].[dbo].[ReaderInfo].[ReaderTitle] as [ri].[ReaderTitle] END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Logical Operation="OR">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Intrinsic FunctionName="len">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </Identifier>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="4091" EstimateCPU="0.0004089" EstimateIO="0.00905093" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="229" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00945983" TableCardinality="229">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="223" ActualRowsRead="223" ActualEndOfScans="0" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="10" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Index="[PK_dbo.ReaderInfo]" Alias="[ri]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore" />
- </IndexScan>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <RelOp AvgRowSize="68" EstimateCPU="0.00164159" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="138.779" LogicalOp="Distinct Sort" NodeId="8" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0202093">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Asset]" Alias="[ass]" Column="SerialNumber" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="133" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <Sort Distinct="true">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Asset]" Alias="[ass]" Column="SerialNumber" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="106" EstimateCPU="0.0003803" EstimateIO="0.0068287" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="138.839" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.007209" TableCardinality="203">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Asset]" Alias="[ass]" Column="SerialNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="133" ActualRowsRead="203" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="8" 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="[dbname]" Schema="[dbo]" Table="[Asset]" Alias="[ass]" Column="SerialNumber" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[Asset]" Index="[PK_dbo.Asset]" Alias="[ass]" IndexKind="Clustered" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[dbname].[dbo].[Asset].[AccountNumber] as [ass].[AccountNumber]=[@accountId]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Asset]" Alias="[ass]" Column="AccountNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@accountId" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Sort>
- </RelOp>
- </Merge>
- </RelOp>
- <RelOp AvgRowSize="4091" EstimateCPU="0.0583224" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58125.9" LogicalOp="Aggregate" NodeId="12" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="33.6788">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="62" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="416" ActualCPUms="416" />
- </RunTimeInformation>
- <StreamAggregate>
- <DefinedValues />
- <GroupBy>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Column="Expr1011" />
- </GroupBy>
- <RelOp AvgRowSize="4091" EstimateCPU="0.271788" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58518.9" LogicalOp="Left Semi Join" NodeId="13" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="33.6205">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="5061" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="407" ActualCPUms="407" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </OuterReferences>
- <RelOp AvgRowSize="4245" EstimateCPU="0.271788" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65021" LogicalOp="Left Outer Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="33.207">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65009" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="271" ActualCPUms="271" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </OuterReferences>
- <RelOp AvgRowSize="4240" EstimateCPU="0.271788" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65021" LogicalOp="Inner Join" NodeId="15" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="22.6522">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65009" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="254" ActualCPUms="254" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </OuterReferences>
- <RelOp AvgRowSize="4154" EstimateCPU="0.271788" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65021" LogicalOp="Inner Join" NodeId="16" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="12.0694">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65021" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="158" ActualCPUms="158" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </OuterReferences>
- <RelOp AvgRowSize="4147" EstimateCPU="0.271788" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65021" LogicalOp="Inner Join" NodeId="17" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.50215">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65021" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="54" ActualCPUms="54" />
- </RunTimeInformation>
- <NestedLoops Optimized="false" WithOrderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Column="Expr1021" />
- </OuterReferences>
- <RelOp AvgRowSize="4091" EstimateCPU="2.29E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="229" LogicalOp="Compute Scalar" NodeId="19" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00948273">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Column="Expr1011" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1011" />
- <ScalarOperator ScalarString="CASE WHEN [dbname].[dbo].[ReaderInfo].[ReaderTitle] as [ri].[ReaderTitle] IS NULL OR len([dbname].[dbo].[ReaderInfo].[ReaderTitle] as [ri].[ReaderTitle])=(0) THEN CONVERT_IMPLICIT(nvarchar(max),[dbname].[dbo].[ReaderInfo].[ReaderSerialNumber] as [ri].[ReaderSerialNumber],0) ELSE [dbname].[dbo].[ReaderInfo].[ReaderTitle] as [ri].[ReaderTitle] END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Logical Operation="OR">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Intrinsic FunctionName="len">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </Identifier>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="4091" EstimateCPU="0.0004089" EstimateIO="0.00905093" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="229" LogicalOp="Clustered Index Scan" NodeId="20" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00945983" TableCardinality="229">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="229" ActualRowsRead="229" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="11" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderTitle" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Index="[PK_dbo.ReaderInfo]" Alias="[ri]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore" />
- </IndexScan>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <RelOp AvgRowSize="65" EstimateCPU="0.000469328" EstimateIO="0.0068287" EstimateRebinds="174.73" EstimateRewinds="53.2704" EstimatedExecutionMode="Row" EstimateRows="283.934" LogicalOp="Index Seek" NodeId="24" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="1.22088" TableCardinality="65021">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65021" ActualRowsRead="65021" ActualEndOfScans="229" ActualExecutions="229" ActualElapsedms="51" ActualCPUms="51" ActualScans="229" ActualLogicalReads="2238" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Index="[IX_ReaderSerialNumber]" Alias="[mt]" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="ReaderSerialNumber" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[ReaderInfo].[ReaderSerialNumber] as [ri].[ReaderSerialNumber]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[ReaderInfo]" Alias="[ri]" Column="ReaderSerialNumber" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="56" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="62016.6" EstimateRewinds="3003.36" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="25" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="10.2954" TableCardinality="103">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65021" ActualRowsRead="65021" ActualEndOfScans="0" ActualExecutions="65021" ActualElapsedms="88" ActualCPUms="88" ActualScans="0" ActualLogicalReads="130042" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[Account]" Index="[PK_dbo.Account]" Alias="[a]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[MastitisTest].[AccountNumber] as [mt].[AccountNumber]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="111" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="25299.9" EstimateRewinds="39720.1" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="10.3111" TableCardinality="268">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65009" ActualRowsRead="65009" ActualEndOfScans="12" ActualExecutions="65021" ActualElapsedms="76" ActualCPUms="76" ActualScans="0" ActualLogicalReads="130018" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Index="[PK_dbo.CustomerLocation]" Alias="[cl]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[MastitisTest].[CustomerLocationID] as [mt].[CustomerLocationID]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="14" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="65017" EstimateRewinds="2.97919" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="27" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="10.2829" TableCardinality="103">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="22" ActualRowsRead="22" ActualEndOfScans="64987" ActualExecutions="65009" ActualElapsedms="8" ActualCPUms="8" ActualScans="0" ActualLogicalReads="44" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[Account]" Index="[PK_dbo.Account]" Alias="[ca]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="AccountNumber" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[CustomerLocation].[ConsumingAccountNumber] as [cl].[ConsumingAccountNumber]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="65020" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Concatenation" NodeId="28" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0.141746">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="5061" ActualEndOfScans="59948" ActualExecutions="65009" ActualElapsedms="97" ActualCPUms="97" />
- </RunTimeInformation>
- <Concat>
- <DefinedValues />
- <RelOp AvgRowSize="9" EstimateCPU="1.08E-06" EstimateIO="0" EstimateRebinds="65020" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="29" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.135244">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="61407" ActualRewinds="3602" ActualRows="5061" ActualEndOfScans="59948" ActualExecutions="65009" ActualElapsedms="11" ActualCPUms="11" />
- </RunTimeInformation>
- <Filter StartupExpression="true">
- <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="65020" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="30" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="0.0650212">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="5061" ActualEndOfScans="0" ActualExecutions="5061" ActualElapsedms="0" ActualCPUms="0" />
- </RunTimeInformation>
- <ConstantScan />
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[dbname].[dbo].[Account].[AccountNumber] as [a].[AccountNumber]=[@accountId] AND ([@country] IS NULL OR [dbname].[dbo].[Account].[Country] as [a].[Country]=[@country])">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@accountId" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Logical Operation="OR">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="ConstExpr1014">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="1.08E-06" EstimateIO="0" EstimateRebinds="65020" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="32" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="5.2491">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="45" ActualRewinds="59903" ActualRows="0" ActualEndOfScans="59948" ActualExecutions="59948" ActualElapsedms="74" ActualCPUms="74" />
- </RunTimeInformation>
- <Filter StartupExpression="true">
- <RelOp AvgRowSize="111" EstimateCPU="7.96E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="65020" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="33" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="5.17888" TableCardinality="0">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRowsRead="239792" ActualEndOfScans="59948" ActualExecutions="59948" ActualElapsedms="70" ActualCPUms="70" ActualScans="1" ActualLogicalReads="59948" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues />
- <Object Table="[@associatedAccountsTable]" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[dbname].[dbo].[CustomerLocation].[ConsumingAccountNumber] as [cl].[ConsumingAccountNumber]=[data]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="data" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </TableScan>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[@country] IS NULL OR [dbname].[dbo].[Account].[Country] as [ca].[Country]=[@country]">
- <Logical Operation="OR">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="ConstExpr1015">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </Concat>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </StreamAggregate>
- </RelOp>
- </Merge>
- </RelOp>
- <ParameterList>
- <ColumnReference Column="@country" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
- <ColumnReference Column="@accountId" ParameterCompiledValue="N'001C00000157N6BIAU'" ParameterRuntimeValue="N'001C00000157N6BIAU'" />
- </ParameterList>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="8" StatementEstRows="43331.9" StatementId="3" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="25.4323" StatementText="select distinct [Batches].ID, [Batches].Notes, [Batches].StartTime, [Batches].CompleteTime, [Batches].ReaderSerialNumber as ReaderSN from TestBatch [Batches]
inner join MastitisTest mt on [Batches].ID = mt.BatchID
inner join Account a on a.AccountNumber = mt.AccountNumber
left join CustomerLocation cl on mt.CustomerLocationID = cl.ID
left join Account ca on ca.AccountNumber = cl.ConsumingAccountNumber
where (a.AccountNumber = @accountId and (@country is null or a.Country = @country)) or
(cl.ConsumingAccountNumber in (select data from @associatedAccountsTable) and (@country is null or ca.Country = @country))" StatementType="SELECT" QueryHash="0x6520480BB3AC705C" QueryPlanHash="0x68460BB204E18555" 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 DegreeOfParallelism="1" MemoryGrant="12032" CachedPlanSize="88" CompileTime="18" CompileCPU="18" CompileMemory="1544">
- <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="12032" RequiredMemory="1536" DesiredMemory="12032" RequestedMemory="12032" GrantWaitTime="0" GrantedMemory="12032" MaxUsedMemory="11328" MaxQueryMemory="2018712" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="208659" EstimatedPagesCached="104329" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="8443160" />
- <RelOp AvgRowSize="4099" EstimateCPU="0.0474125" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="43331.9" LogicalOp="Aggregate" NodeId="0" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="25.4323">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="673" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="383" ActualCPUms="376" />
- </RunTimeInformation>
- <StreamAggregate>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- <ScalarOperator ScalarString="ANY([dbname].[dbo].[TestBatch].[Notes] as [Batches].[Notes])">
- <Aggregate AggType="ANY" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- <ScalarOperator ScalarString="ANY([dbname].[dbo].[TestBatch].[StartTime] as [Batches].[StartTime])">
- <Aggregate AggType="ANY" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- <ScalarOperator ScalarString="ANY([dbname].[dbo].[TestBatch].[CompleteTime] as [Batches].[CompleteTime])">
- <Aggregate AggType="ANY" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- <ScalarOperator ScalarString="ANY([dbname].[dbo].[TestBatch].[ReaderSerialNumber] as [Batches].[ReaderSerialNumber])">
- <Aggregate AggType="ANY" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <GroupBy>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- </GroupBy>
- <RelOp AvgRowSize="4099" EstimateCPU="0.239157" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51493.2" LogicalOp="Left Semi Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.3849">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="4130" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="381" ActualCPUms="374" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </OuterReferences>
- <RelOp AvgRowSize="4254" EstimateCPU="0.239157" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="57214.7" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.021">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="59803" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="255" ActualCPUms="248" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </OuterReferences>
- <RelOp AvgRowSize="4248" EstimateCPU="0.239157" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="57214.7" LogicalOp="Left Outer Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="15.7331">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="59803" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="239" ActualCPUms="232" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </OuterReferences>
- <RelOp AvgRowSize="4162" EstimateCPU="0.166784" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="57214.7" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="6.41708">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="59803" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="137" ActualCPUms="130" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <Merge ManyToMany="false">
- <InnerSideJoinColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="BatchID" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[dbname].[dbo].[TestBatch].[ID] as [Batches].[ID]=[dbname].[dbo].[MastitisTest].[BatchID] as [mt].[BatchID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="BatchID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="4099" EstimateCPU="0.0100669" EstimateIO="0.25794" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9009" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.268007" TableCardinality="9009">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="9009" ActualRowsRead="9009" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="2" ActualCPUms="2" ActualScans="1" ActualLogicalReads="349" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="StartTime" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="CompleteTime" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="Notes" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Alias="[Batches]" Column="ReaderSerialNumber" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[TestBatch]" Index="[PK_dbo.TestBatch]" Alias="[Batches]" IndexKind="Clustered" Storage="RowStore" />
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="88" EstimateCPU="4.77185" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65021" LogicalOp="Sort" NodeId="6" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="5.98229">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="BatchID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </OutputList>
- <Warnings>
- <SpillToTempDb SpillLevel="1" SpilledThreadCount="1" />
- <SortSpillDetails GrantedMemoryKb="10688" UsedMemoryKb="10688" WritesToTempDb="740" ReadsFromTempDb="740" />
- </Warnings>
- <MemoryFractions Input="0.969512" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="65021" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="129" ActualCPUms="122" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="740" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="BatchID" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="88" EstimateCPU="0.529126" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65021" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.19918">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="BatchID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </OutputList>
- <MemoryFractions Input="1" Output="0.0304878" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65021" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="34" ActualCPUms="34" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[dbname].[dbo].[Account].[AccountNumber] as [a].[AccountNumber]=[dbname].[dbo].[MastitisTest].[AccountNumber] as [mt].[AccountNumber]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="56" EstimateCPU="0.0002703" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="103" LogicalOp="Index Scan" NodeId="8" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0033953" TableCardinality="103">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="103" ActualRowsRead="103" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[Account]" Index="[IX_Country]" Alias="[a]" IndexKind="NonClustered" Storage="RowStore" />
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="81" EstimateCPU="0.0716801" EstimateIO="0.594977" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65021" LogicalOp="Index Scan" NodeId="9" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.666657" TableCardinality="65021">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="BatchID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="65021" ActualRowsRead="65021" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="7" ActualCPUms="7" ActualScans="1" ActualLogicalReads="807" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="BatchID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Index="[IX_CustomerLocationID]" Alias="[mt]" IndexKind="NonClustered" Storage="RowStore" />
- </IndexScan>
- </RelOp>
- </Hash>
- </RelOp>
- </Sort>
- </RelOp>
- </Merge>
- </RelOp>
- <RelOp AvgRowSize="111" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="22262.4" EstimateRewinds="34951.3" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="9.07689" TableCardinality="268">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="59791" ActualRowsRead="59791" ActualEndOfScans="12" ActualExecutions="59803" ActualElapsedms="84" ActualCPUms="84" ActualScans="0" ActualLogicalReads="119582" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Index="[PK_dbo.CustomerLocation]" Alias="[cl]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[MastitisTest].[CustomerLocationID] as [mt].[CustomerLocationID]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="14" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="57211" EstimateRewinds="2.62036" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="13" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="9.04876" TableCardinality="103">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="22" ActualRowsRead="22" ActualEndOfScans="59781" ActualExecutions="59803" ActualElapsedms="7" ActualCPUms="7" ActualScans="0" ActualLogicalReads="44" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[Account]" Index="[PK_dbo.Account]" Alias="[ca]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="AccountNumber" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[CustomerLocation].[ConsumingAccountNumber] as [cl].[ConsumingAccountNumber]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="57213.7" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Concatenation" NodeId="14" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0.124728">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="4130" ActualEndOfScans="55673" ActualExecutions="59803" ActualElapsedms="96" ActualCPUms="96" />
- </RunTimeInformation>
- <Concat>
- <DefinedValues />
- <RelOp AvgRowSize="9" EstimateCPU="1.08E-06" EstimateIO="0" EstimateRebinds="57213.7" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="15" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.119007">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="56282" ActualRewinds="3521" ActualRows="4130" ActualEndOfScans="55673" ActualExecutions="59803" ActualElapsedms="8" ActualCPUms="8" />
- </RunTimeInformation>
- <Filter StartupExpression="true">
- <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="57213.7" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="16" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="0.0572148">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="4130" ActualEndOfScans="0" ActualExecutions="4130" ActualElapsedms="0" ActualCPUms="0" />
- </RunTimeInformation>
- <ConstantScan />
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[dbname].[dbo].[Account].[AccountNumber] as [a].[AccountNumber]=[@accountId] AND ([@country] IS NULL OR [dbname].[dbo].[Account].[Country] as [a].[Country]=[@country])">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@accountId" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Logical Operation="OR">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="ConstExpr1008">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="1.08E-06" EstimateIO="0" EstimateRebinds="57213.7" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="18" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="4.61928">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="3" ActualRewinds="55670" ActualRows="0" ActualEndOfScans="55673" ActualExecutions="55673" ActualElapsedms="77" ActualCPUms="77" />
- </RunTimeInformation>
- <Filter StartupExpression="true">
- <RelOp AvgRowSize="111" EstimateCPU="7.96E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="57213.7" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="19" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="4.55749" TableCardinality="0">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRowsRead="222692" ActualEndOfScans="55673" ActualExecutions="55673" ActualElapsedms="73" ActualCPUms="73" ActualScans="1" ActualLogicalReads="55673" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues />
- <Object Table="[@associatedAccountsTable]" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[dbname].[dbo].[CustomerLocation].[ConsumingAccountNumber] as [cl].[ConsumingAccountNumber]=[data]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[cl]" Column="ConsumingAccountNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="data" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </TableScan>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[@country] IS NULL OR [dbname].[dbo].[Account].[Country] as [ca].[Country]=[@country]">
- <Logical Operation="OR">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="ConstExpr1009">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </Concat>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </StreamAggregate>
- </RelOp>
- <ParameterList>
- <ColumnReference Column="@country" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
- <ColumnReference Column="@accountId" ParameterCompiledValue="N'001C00000157N6BIAU'" ParameterRuntimeValue="N'001C00000157N6BIAU'" />
- </ParameterList>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="9" StatementEstRows="1" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0131525" StatementText="select distinct Locations.ID, Locations.Name, Locations.ReaderSerialNumber from CustomerLocation Locations
inner join MastitisTest mt on Locations.ID = mt.CustomerLocationID
inner join Account a on a.AccountNumber = mt.AccountNumber
left join Account ca on ca.AccountNumber = Locations.ConsumingAccountNumber
where Locations.ID <> '00000000-0000-0000-0000-000000000000' and ((a.AccountNumber = @accountId and (@country is null or a.Country = @country)) or
(Locations.ConsumingAccountNumber in (select data from @associatedAccountsTable) and (@country is null or ca.Country = @country)))" StatementType="SELECT" QueryHash="0x5680DCBF74F21B19" QueryPlanHash="0xEE69D86A0F6414BC" 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 DegreeOfParallelism="1" CachedPlanSize="56" CompileTime="5" CompileCPU="5" CompileMemory="984">
- <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="208659" EstimatedPagesCached="104329" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="8443160" />
- <RelOp AvgRowSize="4083" EstimateCPU="1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="0" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0131525">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="12" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="70" ActualCPUms="70" />
- </RunTimeInformation>
- <StreamAggregate>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- <ScalarOperator ScalarString="ANY([dbname].[dbo].[CustomerLocation].[Name] as [Locations].[Name])">
- <Aggregate AggType="ANY" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- <ScalarOperator ScalarString="ANY([dbname].[dbo].[CustomerLocation].[ReaderSerialNumber] as [Locations].[ReaderSerialNumber])">
- <Aggregate AggType="ANY" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <GroupBy>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- </GroupBy>
- <RelOp AvgRowSize="4083" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0131515">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="76" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="69" ActualCPUms="69" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </OuterReferences>
- <RelOp AvgRowSize="4237" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0131449">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="14510" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="37" ActualCPUms="37" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- </OuterReferences>
- <RelOp AvgRowSize="4232" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985766">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="14510" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="32" ActualCPUms="32" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </OuterReferences>
- <RelOp AvgRowSize="4225" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="14510" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="3" ActualCPUms="3" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- </OuterReferences>
- <RelOp AvgRowSize="4185" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="268">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="267" ActualRowsRead="267" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" ActualScans="2" ActualLogicalReads="14" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="Name" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ReaderSerialNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Index="[PK_dbo.CustomerLocation]" Alias="[Locations]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <EndRange ScanType="LT">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="{guid'00000000-0000-0000-0000-000000000000'}">
- <Const ConstValue="{guid'00000000-0000-0000-0000-000000000000'}" />
- </ScalarOperator>
- </RangeExpressions>
- </EndRange>
- </SeekKeys>
- </SeekPredicateNew>
- <SeekPredicateNew>
- <SeekKeys>
- <StartRange ScanType="GT">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="{guid'00000000-0000-0000-0000-000000000000'}">
- <Const ConstValue="{guid'00000000-0000-0000-0000-000000000000'}" />
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="49" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="65021">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="14510" ActualRowsRead="14510" ActualEndOfScans="267" ActualExecutions="267" ActualElapsedms="3" ActualCPUms="3" ActualScans="267" ActualLogicalReads="1026" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Index="[IX_CustomerLocationID]" Alias="[mt]" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="CustomerLocationID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[CustomerLocation].[ID] as [Locations].[ID]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="56" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="103">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="14510" ActualRowsRead="14510" ActualEndOfScans="0" ActualExecutions="14510" ActualElapsedms="24" ActualCPUms="24" ActualScans="0" ActualLogicalReads="29020" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[Account]" Index="[PK_dbo.Account]" Alias="[a]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[MastitisTest].[AccountNumber] as [mt].[AccountNumber]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[MastitisTest]" Alias="[mt]" Column="AccountNumber" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="14" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="103">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="22" ActualRowsRead="22" ActualEndOfScans="14488" ActualExecutions="14510" ActualElapsedms="3" ActualCPUms="3" ActualScans="0" ActualLogicalReads="44" 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>
- <DefinedValue>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[dbname]" Schema="[dbo]" Table="[Account]" Index="[PK_dbo.Account]" Alias="[ca]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="AccountNumber" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[dbname].[dbo].[CustomerLocation].[ConsumingAccountNumber] as [Locations].[ConsumingAccountNumber]">
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Concatenation" NodeId="9" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="2.337E-06">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="76" ActualEndOfScans="14434" ActualExecutions="14510" ActualElapsedms="24" ActualCPUms="24" />
- </RunTimeInformation>
- <Concat>
- <DefinedValues />
- <RelOp AvgRowSize="9" EstimateCPU="1.08E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="10" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="2.237E-06">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="14446" ActualRewinds="64" ActualRows="76" ActualEndOfScans="14434" ActualExecutions="14510" ActualElapsedms="1" ActualCPUms="1" />
- </RunTimeInformation>
- <Filter StartupExpression="true">
- <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="11" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="76" ActualEndOfScans="0" ActualExecutions="76" ActualElapsedms="0" ActualCPUms="0" />
- </RunTimeInformation>
- <ConstantScan />
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[dbname].[dbo].[Account].[AccountNumber] as [a].[AccountNumber]=[@accountId] AND ([@country] IS NULL OR [dbname].[dbo].[Account].[Country] as [a].[Country]=[@country])">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="AccountNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@accountId" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Logical Operation="OR">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="ConstExpr1007">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[a]" Column="Country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="1.08E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="13" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.00328418">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="3" ActualRewinds="14431" ActualRows="0" ActualEndOfScans="14434" ActualExecutions="14434" ActualElapsedms="20" ActualCPUms="20" />
- </RunTimeInformation>
- <Filter StartupExpression="true">
- <RelOp AvgRowSize="111" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="14" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRowsRead="57736" ActualEndOfScans="14434" ActualExecutions="14434" ActualElapsedms="18" ActualCPUms="18" ActualScans="1" ActualLogicalReads="14434" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues />
- <Object Table="[@associatedAccountsTable]" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[dbname].[dbo].[CustomerLocation].[ConsumingAccountNumber] as [Locations].[ConsumingAccountNumber]=[data]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[CustomerLocation]" Alias="[Locations]" Column="ConsumingAccountNumber" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="data" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </TableScan>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[@country] IS NULL OR [dbname].[dbo].[Account].[Country] as [ca].[Country]=[@country]">
- <Logical Operation="OR">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="ConstExpr1008">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[Account]" Alias="[ca]" Column="Country" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@country" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </Concat>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </StreamAggregate>
- </RelOp>
- <ParameterList>
- <ColumnReference Column="@country" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
- <ColumnReference Column="@accountId" ParameterCompiledValue="N'001C00000157N6BIAU'" ParameterRuntimeValue="N'001C00000157N6BIAU'" />
- </ParameterList>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="10" StatementEstRows="1" StatementId="5" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="1.157E-06" StatementText="select PenNumber as PenName, PenNumber as PenValue from BloodTest Pens where 1 = 0" StatementType="SELECT" QueryHash="0x787E43F2D0D0BB3D" QueryPlanHash="0xF460CA0DAEE7250" 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 DegreeOfParallelism="1" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="496">
- <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="208659" EstimatedPagesCached="104329" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="8443160" />
- <RelOp AvgRowSize="61" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="0" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[BloodTest]" Alias="[Pens]" Column="PenNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" />
- </RunTimeInformation>
- <ConstantScan />
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="11" StatementEstRows="1" StatementId="6" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="1.157E-06" StatementText="select LotNumber as LotName, LotNumber as LotValue from BloodTest Pens where 1 = 0" StatementType="SELECT" QueryHash="0xB2CF5A53A3B57CEC" QueryPlanHash="0x54D9FCEAFC60A130" 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 DegreeOfParallelism="1" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="496">
- <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="208659" EstimatedPagesCached="104329" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="8443160" />
- <RelOp AvgRowSize="61" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="0" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
- <OutputList>
- <ColumnReference Database="[dbname]" Schema="[dbo]" Table="[BloodTest]" Alias="[Pens]" Column="LotNumber" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="0" ActualCPUms="0" />
- </RunTimeInformation>
- <ConstantScan />
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement