November 11, 2011 at 3:41 pm
I'm stumped, but I have been sick most of the week so maybe my brain is just failing me today. I searched before posting, but failed to find the criteria that got me relevant posts.
SELECT a.idTest
FROM vwMostRecentInstanceOfEachStatusPerTest a
INNER JOIN
Test b ON a.idTest=b.idTest
AND a.idTestStatus=b.idTestStatus
This returns 10 rows.
SELECT a.idTest
,*
FROM vwMostRecentInstanceOfEachStatusPerTest a
INNER JOIN
Test b ON a.idTest=b.idTest
AND a.idTestStatus=b.idTestStatus
This returns 5 rows.
It appears that including * in the select list somehow lets the join to what I expect, but using only specific columns does not.
If it matters vwMostRecentInstanceOfEachStatusPerTest is based on:
row_number() OVER(PARTITION BY idTest, idTestStatus ORDER BY idTest, idTestStatus DESC) AS StatusRank and eventually filters the results by:
WHERE x.StatusRank=1
What am I missing?
November 12, 2011 at 5:02 am
Can you please post the results of following?
SELECT a.idTest
,a.*
FROM vwMostRecentInstanceOfEachStatusPerTest a
INNER JOIN
Test b ON a.idTest=b.idTest
AND a.idTestStatus=b.idTestStatus
SELECT a.idTest
,b.*
FROM vwMostRecentInstanceOfEachStatusPerTest a
INNER JOIN
Test b ON a.idTest=b.idTest
AND a.idTestStatus=b.idTestStatus
November 12, 2011 at 7:19 am
Dev (11/12/2011)
Can you please post the results of following?
SELECT a.idTest,a.*
FROM vwMostRecentInstanceOfEachStatusPerTest a
INNER JOIN
Test b ON a.idTest=b.idTest
AND a.idTestStatus=b.idTestStatus
5 rows.
SELECT a.idTest,b.*
FROM vwMostRecentInstanceOfEachStatusPerTest a
INNER JOIN
Test b ON a.idTest=b.idTest
AND a.idTestStatus=b.idTestStatus
10 rows.
November 12, 2011 at 7:57 am
Please post the actual execution plan for both versions.
November 12, 2011 at 9:27 am
Please post the actual execution plan for both versions.
I changed the names and shortened a bit to post. The execution plans reflect my actual data. Would it have been better to attach them as files?
What I did notice right off that bat was that second plan does not contain the PhysicalOp="Sequence Project" which makes sense given to results.
Ninja Edit: More digging here. It isn't about the inclusion of the * in the selected list, but rather the inclusion of a particular column a.StatusRank which is the row_number() column referred to in the OP.
Thank you.
SELECT a.idTest
,a.*
FROM vwMostRecentInstanceOfEachStatusPerTest a
INNER JOIN
Test b ON a.idTest=b.idTest
AND a.idTestStatus=b.idTestStatus
<?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.1" Build="10.50.1600.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="5" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0401368" StatementText="SELECT a.idTest ,a.* FROM TestHistoryRankedByTestAndStatus_Rank1 a INNER JOIN LoadTestItem b ON a.idTest=b.LID AND a.idTestStatus=b.Status" StatementType="SELECT" QueryHash="0x70B01AB7AEA57F7F" QueryPlanHash="0x1448104366D623A9">
<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" MemoryGrant="1024" CachedPlanSize="40" CompileTime="5" CompileCPU="5" CompileMemory="360">
<RelOp AvgRowSize="70" EstimateCPU="2.09E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0401368">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="UserName" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="TestStatus" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="Bmk1009" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
</OuterReferences>
<RelOp AvgRowSize="76" EstimateCPU="9.78887E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0362004">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="UserName" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="TestStatus" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
<ColumnReference Column="Bmk1009" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="40" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</OuterReferences>
<RelOp AvgRowSize="68" EstimateCPU="2.34183E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.4183" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.029275">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="UserName" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="TestStatus" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="isnull([Sample].[dbo].[TestHistory].[ActualDate] as [h].[ActualDate],[Sample].[dbo].[TestHistory].[StatusDate] as [h].[StatusDate])">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="ActualDate" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="StatusDate" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="72" EstimateCPU="9.78887E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.4183" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0292727">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="StatusDate" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="UserName" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="TestStatus" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="ActualDate" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="50" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="Uniq1002" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</OuterReferences>
<RelOp AvgRowSize="23" EstimateCPU="9.78887E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.4183" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0223474">
<OutputList>
<ColumnReference Column="Uniq1002" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="50" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
</OuterReferences>
<RelOp AvgRowSize="19" EstimateCPU="3.216E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.4183" LogicalOp="Filter" NodeId="5" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.015422">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="50" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="5.36E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="67" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="0.0153899">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="67" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<SequenceProject>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="row_number">
<Sequence FunctionName="row_number" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="19" EstimateCPU="1.34E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="67" LogicalOp="Segment" NodeId="7" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="0.0153845">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestStatus" />
<ColumnReference Column="Segment1012" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="67" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Segment>
<GroupBy>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestStatus" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1012" />
</SegmentColumn>
<RelOp AvgRowSize="17" EstimateCPU="0.000734045" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="67" LogicalOp="Sort" NodeId="8" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0153832">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestStatus" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="67" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestStatus" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="33" EstimateCPU="0.0002307" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="67" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0033557" TableCardinality="67">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestStatus" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="67" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestStatus" />
</DefinedValue>
</DefinedValues>
<Object Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Index="[idx_TestHistory_idTest]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[TestStatus] IS NOT NULL">
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="TestStatus" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Segment>
</RelOp>
</SequenceProject>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="[Expr1007]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="22.4183" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="14" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00682744" TableCardinality="67">
<OutputList>
<ColumnReference Column="Uniq1002" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="50" ActualEndOfScans="0" ActualExecutions="50" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Uniq1002" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</DefinedValue>
</DefinedValues>
<Object Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Index="[PK_TestHistory_idTestHistory]" Alias="[h]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[idTestHistory]">
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="58" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="22.4183" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="16" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00682744" TableCardinality="67">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="StatusDate" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="UserName" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="TestStatus" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="ActualDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="50" ActualEndOfScans="0" ActualExecutions="50" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="StatusDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="UserName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="TestStatus" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="ActualDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Index="[idx_TestHistory_idTest]" Alias="[h]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Column="Uniq1002" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[idTest] as [h].[idTest]">
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Uniq1002]">
<Identifier>
<ColumnReference Column="Uniq1002" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="3.55271E-15" EstimateRewinds="22.4183" EstimateRows="1" LogicalOp="Index Seek" NodeId="31" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00682744" TableCardinality="5">
<OutputList>
<ColumnReference Column="Bmk1009" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="40" ActualEndOfScans="10" ActualExecutions="50" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1009" />
</DefinedValue>
</DefinedValues>
<Object Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Index="[LID]" Alias="" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[idTest] as [h].[idTest]">
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="4" EstimateRewinds="0" EstimateRows="5" LogicalOp="RID Lookup" NodeId="33" Parallel="false" PhysicalOp="RID Lookup" EstimatedTotalSubtreeCost="0.0039155" TableCardinality="5">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="35" ActualExecutions="40" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" TableReferenceId="-1" IndexKind="Heap" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="Bmk1009" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Bmk1009]">
<Identifier>
<ColumnReference Column="Bmk1009" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[idTestStatus] as [h].[idTestStatus]=[Sample].[dbo].[LoadTestItem].[Status] as .[Status]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
SELECT a.idTest
,b.*
FROM vwMostRecentInstanceOfEachStatusPerTest a
INNER JOIN
Test b ON a.idTest=b.idTest
AND a.idTestStatus=b.idTestStatus
<?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.1" Build="10.50.1600.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="5" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0151065" StatementText="SELECT a.idTest ,b.* FROM TestHistoryRankedByTestAndStatus_Rank1 a INNER JOIN LoadTestItem b ON a.idTest=b.LID AND a.idTestStatus=b.Status" StatementType="SELECT" QueryHash="0xD66163D0E154EC70" QueryPlanHash="0x99234BB9FD3E2343">
<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" CachedPlanSize="56" CompileTime="29" CompileCPU="29" CompileMemory="496">
<RelOp AvgRowSize="473" EstimateCPU="5E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Aggregate" NodeId="0" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0151065">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="ID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Loc" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Elev" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Last" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Next" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Ticket" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Remarks" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LastDue" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="idRolCustomContact" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom1" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom2" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom3" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom4" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom5" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom6" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom7" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom8" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom9" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom10" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[TestHistory].[idTest] as [h].[idTest])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[LID] as .[LID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="ID" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[ID] as .[ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Loc" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Loc] as .[Loc])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Loc" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Elev" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Elev] as .[Elev])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Elev" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Last" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Last] as .[Last])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Last" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Next" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Next] as .[Next])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Next" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Status] as .[Status])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Ticket" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Ticket] as .[Ticket])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Ticket" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Remarks" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Remarks] as .[Remarks])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Remarks" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LastDue" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[LastDue] as .[LastDue])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LastDue" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="idRolCustomContact" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[idRolCustomContact] as .[idRolCustomContact])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="idRolCustomContact" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom1" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom1] as .[Custom1])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom1" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom2" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom2] as .[Custom2])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom2" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom3" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom3] as .[Custom3])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom3" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom4" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom4] as .[Custom4])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom4" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom5" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom5] as .[Custom5])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom5" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom6" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom6] as .[Custom6])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom6" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom7" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom7] as .[Custom7])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom7" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom8" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom8] as .[Custom8])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom8" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom9" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom9] as .[Custom9])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom9" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom10" />
<ScalarOperator ScalarString="ANY([Sample].[dbo].[LoadTestItem].[Custom10] as .[Custom10])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom10" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<GroupBy>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
</GroupBy>
<RelOp AvgRowSize="491" EstimateCPU="2.09E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0150991">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="ID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Loc" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Elev" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Last" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Next" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Ticket" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Remarks" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LastDue" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="idRolCustomContact" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom1" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom2" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom3" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom4" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom5" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom6" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom7" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom8" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom9" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom10" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="Uniq1005" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
</OuterReferences>
<RelOp AvgRowSize="486" EstimateCPU="2.09E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0111627">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Column="Uniq1005" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="ID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Loc" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Elev" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Last" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Next" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Ticket" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Remarks" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LastDue" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="idRolCustomContact" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom1" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom2" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom3" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom4" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom5" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom6" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom7" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom8" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom9" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom10" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
</OuterReferences>
<RelOp AvgRowSize="477" EstimateCPU="2.09E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0072263">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="ID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Loc" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Elev" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Last" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Next" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Ticket" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Remarks" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LastDue" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="idRolCustomContact" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom1" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom2" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom3" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom4" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom5" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom6" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom7" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom8" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom9" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom10" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
</OuterReferences>
<RelOp AvgRowSize="469" EstimateCPU="0.0001625" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Table Scan" NodeId="4" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032875" TableCardinality="5">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="ID" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Loc" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Elev" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Last" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Next" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Ticket" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Remarks" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LastDue" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="idRolCustomContact" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom1" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom2" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom3" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom4" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom5" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom6" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom7" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom8" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom9" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom10" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Loc" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Elev" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Last" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Next" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Ticket" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Remarks" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LastDue" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="idRolCustomContact" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom2" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom3" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom4" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom5" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom6" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom7" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom8" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom9" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Custom10" />
</DefinedValue>
</DefinedValues>
<Object Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" IndexKind="Heap" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="17" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="4" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0039155" TableCardinality="67">
<OutputList>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="5" ActualExecutions="5" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</DefinedValue>
</DefinedValues>
<Object Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Index="[idx_TestHistory_idTest]" Alias="[h]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTest" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Sample].[dbo].[LoadTestItem].[LID] as .[LID]">
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="LID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[idTestStatus] as [h].[idTestStatus]=[Sample].[dbo].[LoadTestItem].[Status] as .[Status]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestStatus" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[LoadTestItem]" Alias="" Column="Status" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="4" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0039155" TableCardinality="67">
<OutputList>
<ColumnReference Column="Uniq1005" />
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="0" ActualExecutions="10" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Uniq1005" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
</DefinedValue>
</DefinedValues>
<Object Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Index="[PK_TestHistory_idTestHistory]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTestHistory" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[idTestHistory] as [h].[idTestHistory]">
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Alias="[h]" Column="idTestHistory" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="21" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="4" EstimateRewinds="0" EstimateRows="5" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0039155" TableCardinality="67">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="0" ActualExecutions="10" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Index="[idx_TestHistory_idTest]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
<ColumnReference Column="Uniq1005" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[idTest]">
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="idTest" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Uniq1005]">
<Identifier>
<ColumnReference Column="Uniq1005" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Sample].[dbo].[TestHistory].[TestStatus] IS NOT NULL">
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Sample]" Schema="[dbo]" Table="[TestHistory]" Column="TestStatus" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</StreamAggregate>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
November 12, 2011 at 12:30 pm
Yes please attach both versions. It's much easier that way.
November 12, 2011 at 1:16 pm
Attaching. No longer using the *, but just including StatusRank or not.
November 12, 2011 at 4:40 pm
Can you post the view definition?
November 12, 2011 at 7:10 pm
Ninja's_RGR'us (11/12/2011)
Can you post the view definition?
Again the real code, not the sanitized version in the OP.
CREATE VIEW TestHistoryRankedByTestAndStatus_Rank1 AS
SELECT h.idTest
,h.idTestStatus
,h.TestStatus
,h.UserName
,ISNULL(h.ActualDate,h.StatusDate) StatusDate
,x.StatusRank --if I didn't inlude this the where d
FROM TestHistory h
INNER JOIN
(
SELECT idTestHistory
,row_number() OVER(PARTITION BY idTest, idTestStatus ORDER BY idTest, idTestStatus DESC) AS StatusRank
FROM TestHistory
WHERE TestStatus IS NOT NULL
) x ON h.idTestHistory=x.idTestHistory
WHERE x.StatusRank=1
GO
November 12, 2011 at 7:31 pm
This might be a bug, I'll have to spend some time on it tomorrow.
What's that code supposed to do anyways? The query looks weird to me.
November 12, 2011 at 10:23 pm
Ninja's_RGR'us (11/12/2011)
This might be a bug, I'll have to spend some time on it tomorrow.What's that code supposed to do anyways? The query looks weird to me.
I do appreciate the time.
Briefly (using more generic names):
I have a table Item with a Status field on it and another table ItemStatusHistory that tracks the datetime* of each status change for each row in table Item. Each record can be in any status multiple times.
The row_order in the subquery ranks each row in ItemStatusHistory by the datetime of the Status change partitioned by the record in table Item and the Status. This is returned as StatusRank.
The outer select in the view filters the subquery results down to only the most recent entry in to a each status for each item--results with a StatusRank of 1.
*The automatic datetime can be overridden via my front-end which is stored in ActualDate. This is used when the real word event actually happened on a date prior to the the change in the database. So ISNULL(h.ActualDate,h.StatusDate) StatusDate choose the user provided date if available and otherwise uses the system date.
November 13, 2011 at 6:28 am
Thanks for the info I can't be guessing around here.
Please post the table create script including keys & indexes.
Bugs are usually super edge cases with a lot a special requirement to hit the bug. I don't have time to waste 5 hours on thin air for you ;-).
Also what does this return on the server you're getting the data from?
SELECT @@VERSION
November 13, 2011 at 8:55 am
Bugs are usually super edge cases with a lot a special requirement to hit the bug. I don't have time to waste 5 hours on thin air for you ;-).
Fair.
Also what does this return on the server you're getting the data from?
SELECT @@VERSION
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
I scripted the structure and data of the two table and the view involved. I ran the script on a new db and then tried the problem queries. They did not show the anomalous behavior.
I saved a backup of the original db and restored it to a test db. I ran the pair of queries and again got the bad results. I ran UPDATE STATISTICS TestHistoryand reran the queries. They did not show the anomalous behavior.
Restoring again for demo purposes it looks like this:
SELECT COUNT(a.StatusRank) RecordCount
FROM TestHistoryRankedByTestAndStatus_Rank1 a
INNER JOIN
LoadTestItem b ON a.idTest=b.LID
AND a.idTestStatus=b.Status
Results:
RecordCount
10
SELECT a.StatusRank
FROM TestHistoryRankedByTestAndStatus_Rank1 a
INNER JOIN
LoadTestItem b ON a.idTest=b.LID
AND a.idTestStatus=b.Status
Results:
StatusRank
1
1
1
1
1
Now I update stats and rerun.
SELECT COUNT(a.StatusRank) RecordCount
FROM TestHistoryRankedByTestAndStatus_Rank1 a
INNER JOIN
LoadTestItem b ON a.idTest=b.LID
AND a.idTestStatus=b.Status
Results:
RecordCount
5
SELECT a.StatusRank
FROM TestHistoryRankedByTestAndStatus_Rank1 a
INNER JOIN
LoadTestItem b ON a.idTest=b.LID
AND a.idTestStatus=b.Status
Results:
StatusRank
1
1
1
1
1
So apparently something in the statistics is causing anomalous query results, but that is much harder to recreate. At least, without understanding much more about SQL Server inner workings with regard to how it builds and uses statistics for querying.
Thoughts on how to proceed?
November 13, 2011 at 9:14 am
Karl Kieninger (11/12/2011)
The row_order in the subquery ranks each row in ItemStatusHistory by the datetime of the Status change partitioned by the record in table Item and the Status. This is returned as StatusRank.
Looked at this quickly. I think the query optimizer is exposing a bug in your view. The rank definition is:
OVER(PARTITION BY idTest, idTestStatus ORDER BY idTest, idTestStatus DESC)
No mention of a datetime in the ORDER BY ... DESC part. As written, the PARTITION BY and ORDER BY clauses are identical. Combined with the rank = 1 predicate later, the QO can rewrite this as an ANY aggregate. (See http://sqlblog.com/blogs/paul_white/archive/2011/07/02/undocumented-query-plans-the-any-aggregate.aspx)
edit: formatting
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 13, 2011 at 12:17 pm
SQL Kiwi (11/13/2011)
Karl Kieninger (11/12/2011)
The row_order in the subquery ranks each row in ItemStatusHistory by the datetime of the Status change partitioned by the record in table Item and the Status. This is returned as StatusRank.Looked at this quickly. I think the query optimizer is exposing a bug in your view. The rank definition is:
OVER(PARTITION BY idTest, idTestStatus ORDER BY idTest, idTestStatus DESC)No mention of a datetime in the ORDER BY ... DESC part.
True enough. I am using (ok, should be using) the identity column in place of the datetime column. In this case they will produce equivalent order.
As written, the PARTITION BY and ORDER BY clauses are identical. Combined with the rank = 1 predicate later, the QO can rewrite this as an ANY aggregate. (See http://sqlblog.com/blogs/paul_white/archive/2011/07/02/undocumented-query-plans-the-any-aggregate.aspx)
Very interesting. The most relevant part of you article being:
If you want to take advantage of the ANY aggregate rewrite, you have to be careful to match the conditions for the rewrite exactly. Even including the ROW_NUMBER column (rn) in the outer SELECT is enough to break the rule matching. It really is quite sensitive, bless it.
That seems to be exactly what is happening as I should have a written:
row_number() OVER(PARTITION BY idTest, idTestStatus ORDER BY idTestHistory DESC) using the identity as mentioned above.
And that does seem to fix the problem, but still leaves me confused about the behavior.
By writing the OVER as I did originally I was using the equivalent of an ANY instead of getting the row order I wanted. I confirmed that another way by looking closely at my returns when I do get the correct number of results. I do not get the record I want (most recent), but instead get an arbitrary record for row number 1. That makes complete sense since my ORDER BY is basically meaningless within my PARTITION.
Note, however, that QO was not actually using the ANY replacement because I did not have an index equivalent to yourCREATE INDEX nc1 ON #Example (col1, col2, thing);
When I add CREATE INDEX nc1 ON TestHistory (idTest, idTestStatus, UserName);I do get the Stream Aggregate.
I remain unclear on:
1. I should be getting a single ANY row back for each regardless of my select list, but instead get multiples back in some cases. The WHERE x.StatusRank=1 should give me just one of each partition (even if it isn't the one I want).
2. Until I UPDATE STATISTICS and then it works does give one of each.
Thanks.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply