Performance Problems

Hello,

i measure times for a delete, insert and select in a database with a c# program (.net). If i measure times for delete 5.000 data records I get very good times but if I delete 100 data records in a other database I get very bad times.

Example:

Statement: Delete from xy where id =1;

data rows delete : Time:
100 73.045
500 332.238
1000 636.075
5000 36.082
50000 308.774

The same time jumps I get for a select. But only for this big select:

for exmaple:

Select id, (Select name from Gemeinden inner join Hochschulen where distance>=Hochschulen.entfernung and distance <Hochschulen.entfernung ), (Select name from H?user inner join Hochschulen where distance>=Hochschulen.entfernung and distance <Hochschulen.entfernung ) from Hochulen inner Join Uni on Hochschulen.id=Uni.id...

What is the reason for this big time jump? If i update the statistic I get better times for a delete but not for this big select.

Can I change I size of a page (8KB)?

I hope somebody can help my!!

(I use MS SQL Server 2005)

Greeting finchen

The page size is fixed so you cannot change it from 8KB and there is no reason why you would want to either.

What is the query plan of these queries? Have you checked the missing indexes dmv?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Do you want to see the query plan? On the tables are indexes but I dont know if he use the indexes.

|||It could help us if we see the query plan, so go ahead if you wish to show it.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||If you want to share the Querylan with us, please share it as a SQLPLAN file. SqlPlan Files are XML based queryplans which can be easily imported by any SSMS Studio (No more text debugging :-) )

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

One of the nifty features of SSMS.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The first plan with 500 data records this is the select:

-

<?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.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="17.0326" StatementText="SELECT ILI_DATA.ILI_DATA_ID,STATION_POINT_2.MEASURE,ILI_DATA.LENGTH, ILI_DATA.WIDTH, ILI_DATA.Nominal_WALL_THICKNESS, ILI_DATA.PIPE_SMYS, ILI_DATA.B31G_MAOP, ILI_DATA.ORIENTATION_DEG,&#xD;&#xA;(SELECT PIPE_SEGMENT.NOMINAL_DIAMETER_GCL&#xD;&#xA;FROM STATION_POINT as s1 INNER JOIN&#xD;&#xA; EVENT_RANGE ON s1.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN&#xD;&#xA; STATION_POINT AS s2 ON EVENT_RANGE.STATION_ID_END = s2.STATION_ID AND &#xD;&#xA; s1.ROUTE_ID = s2.ROUTE_ID INNER JOIN&#xD;&#xA; PIPE_SEGMENT ON EVENT_RANGE.EVENT_ID = PIPE_SEGMENT.EVENT_ID&#xD;&#xA;WHERE (s1.MEASURE &lt; STATION_POINT_2.MEASURE) AND (s2.MEASURE &gt; STATION_POINT_2.MEASURE) AND (s1.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Nominal_Diameter,&#xD;&#xA;(SELECT ILI_PIPE_LENGTH.Measured_Wall_Thickness&#xD;&#xA;FROM STATION_POINT AS s3 INNER JOIN&#xD;&#xA; EVENT_RANGE ON s3.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN&#xD;&#xA; STATION_POINT AS s4 ON EVENT_RANGE.STATION_ID_END = s4.STATION_ID AND s3.ROUTE_ID = s4.ROUTE_ID INNER JOIN&#xD;&#xA; ILI_PIPE_LENGTH ON EVENT_RANGE.EVENT_ID = ILI_PIPE_LENGTH.EVENT_ID&#xD;&#xA;WHERE (s3.MEASURE &lt; STATION_POINT_2.MEASURE) AND (s4.MEASURE &gt; STATION_POINT_2.MEASURE) AND (s3.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Measured_Wall_Thickness&#xD;&#xA;FROM EVENT_RANGE INNER JOIN&#xD;&#xA; ILI_DATA ON EVENT_RANGE.EVENT_ID = ILI_DATA.EVENT_ID INNER JOIN&#xD;&#xA; STATION_POINT AS STATION_POINT_2 ON EVENT_RANGE.STATION_ID_BEGIN = STATION_POINT_2.STATION_ID AND &#xD;&#xA; EVENT_RANGE.STATION_ID_END = STATION_POINT_2.STATION_ID&#xD;&#xA;WHERE (ILI_DATA.ILI_INSPECTION_ID = 22)" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="153">
<MissingIndexes>
<MissingIndexGroup Impact="57.3934">
<MissingIndex Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[ILI_INSPECTION_ID]" ColumnId="61" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[ILI_DATA_ID]" ColumnId="1" />
<Column Name="[EVENT_ID]" ColumnId="3" />
<Column Name="[LENGTH]" ColumnId="21" />
<Column Name="[WIDTH]" ColumnId="22" />
<Column Name="[ORIENTATION_DEG]" ColumnId="23" />
<Column Name="[NOMINAL_WALL_THICKNESS]" ColumnId="33" />
<Column Name="[PIPE_SMYS]" ColumnId="34" />
<Column Name="[B31G_MAOP]" ColumnId="35" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="66" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="17.0326">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
<ColumnReference Column="Expr1035" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="[Expr1033]">
<Identifier>
<ColumnReference Column="Expr1033" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="[Expr1035]">
<Identifier>
<ColumnReference Column="Expr1035" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="66" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="17.0326">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
<ColumnReference Column="Expr1035" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OuterReferences>
<RelOp AvgRowSize="70" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="17.0194">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OuterReferences>
<RelOp AvgRowSize="65" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="9.90674">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OuterReferences>
<RelOp AvgRowSize="55" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="9.90346">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</OuterReferences>
<RelOp AvgRowSize="65" EstimateCPU="0.172813" EstimateIO="9.65201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="9.82483">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[ILI_INSPECTION_ID]=(22.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_INSPECTION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(22.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]=[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[STATION_POINT_2]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="12" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="10" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="7.11266">
<OutputList>
<ColumnReference Column="Expr1033" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="20" EstimateCPU="0.000560787" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="11" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="7.11266">
<OutputList>
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1033" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1032" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="ANY([Perform_Clustern_PODS_100].[dbo].[PIPE_SEGMENT].[NOMINAL_DIAMETER_GCL])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="0.0919875" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="933.812" LogicalOp="Inner Join" NodeId="12" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="7.1121">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_END]=[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s2].[STATION_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="21" EstimateCPU="0.32816" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="933.817" LogicalOp="Inner Join" NodeId="13" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="6.03068">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<MemoryFractions Input="0.226601" Output="0.987685" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[EVENT_ID]=[Perform_Clustern_PODS_100].[dbo].[PIPE_SEGMENT].[EVENT_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="25" EstimateCPU="0.0447123" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10696.7" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="5.56526">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<NestedLoops Optimized="true" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
<ColumnReference Column="Expr1041" />
</OuterReferences>
<RelOp AvgRowSize="16" EstimateCPU="0.0447123" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10696.7" LogicalOp="Inner Join" NodeId="17" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3.82487">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</OutputList>
<NestedLoops Optimized="true" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
<ColumnReference Column="Expr1040" />
</OuterReferences>
<RelOp AvgRowSize="16" EstimateCPU="0.770638" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8444.82" LogicalOp="Inner Join" NodeId="21" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.989431">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</OutputList>
<MemoryFractions Input="0.987685" Output="0.615764" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s1].[STATION_ID] = [Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s1].[STATION_ID]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="16" EstimateCPU="0.0311213" EstimateIO="0.0571991" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="28149.4" LogicalOp="Index Seek" NodeId="22" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0883204">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[IX_STATION_POINT_ROUTE_ID]" Alias="[s1]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="ROUTE_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.0466035" EstimateIO="0.0838657" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="42224.1" LogicalOp="Index Seek" NodeId="23" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.130469">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[STATION_POINT_FK3]" Alias="[s1]" />
<SeekPredicates>
<SeekPredicate>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.000158393" EstimateIO="0.003125" EstimateRebinds="8443.82" EstimateRewinds="0" EstimateRows="1.26666" LogicalOp="Index Seek" NodeId="28" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="2.79073">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[EVENT_RANGE_FK2]" TableReferenceId="2" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s1].[STATION_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="2252.05" EstimateRewinds="8443.68" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="30" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1.69568">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="21" EstimateCPU="0.0126475" EstimateIO="0.124606" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11355" LogicalOp="Clustered Index Scan" NodeId="34" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.137254">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Index="[PK_PIPE_SEGMENT]" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.770638" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8444.82" LogicalOp="Inner Join" NodeId="37" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.989431">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</OutputList>
<MemoryFractions Input="0.987685" Output="0.987685" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s2].[STATION_ID] = [Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s2].[STATION_ID]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="16" EstimateCPU="0.0311213" EstimateIO="0.0571991" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="28149.4" LogicalOp="Index Seek" NodeId="38" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0883204">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[IX_STATION_POINT_ROUTE_ID]" Alias="[s2]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="ROUTE_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.0466035" EstimateIO="0.0838657" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="42224.1" LogicalOp="Index Seek" NodeId="39" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.130469">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[STATION_POINT_FK3]" Alias="[s2]" />
<SeekPredicates>
<SeekPredicate>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1032]&gt;(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1032" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="12" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="49" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.013148">
<OutputList>
<ColumnReference Column="Expr1035" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="20" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="50" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0131478">
<OutputList>
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1034" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="ANY([Perform_Clustern_PODS_100].[dbo].[ILI_PIPE_LENGTH].[MEASURED_WALL_THICKNESS])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="51" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0131467">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="53" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985854">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</OuterReferences>
<RelOp AvgRowSize="30" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="55" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="57" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Index="[PK_ILI_PIPE_LENGTH]" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="58" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="3" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_PIPE_LENGTH].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="59" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s3]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [s3].[ROUTE_ID]=[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID] AND [Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [s3].[MEASURE]&lt;[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="60" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s4]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [s4].[ROUTE_ID]=[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID] AND [Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [s4].[MEASURE]&gt;[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1034]&gt;(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1034" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

--

the plan with good times and 250.000 data records(select):

-

<?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.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="239286" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="60412.5" StatementText="SELECT ILI_DATA.ILI_DATA_ID,STATION_POINT_2.MEASURE,ILI_DATA.LENGTH, ILI_DATA.WIDTH, ILI_DATA.Nominal_WALL_THICKNESS, ILI_DATA.PIPE_SMYS, ILI_DATA.B31G_MAOP, ILI_DATA.ORIENTATION_DEG,&#xD;&#xA;(SELECT PIPE_SEGMENT.NOMINAL_DIAMETER_GCL&#xD;&#xA;FROM STATION_POINT as s1 INNER JOIN&#xD;&#xA; EVENT_RANGE ON s1.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN&#xD;&#xA; STATION_POINT AS s2 ON EVENT_RANGE.STATION_ID_END = s2.STATION_ID AND &#xD;&#xA; s1.ROUTE_ID = s2.ROUTE_ID INNER JOIN&#xD;&#xA; PIPE_SEGMENT ON EVENT_RANGE.EVENT_ID = PIPE_SEGMENT.EVENT_ID&#xD;&#xA;WHERE (s1.MEASURE &lt; STATION_POINT_2.MEASURE) AND (s2.MEASURE &gt; STATION_POINT_2.MEASURE) AND (s1.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Nominal_Diameter,&#xD;&#xA;(SELECT ILI_PIPE_LENGTH.Measured_Wall_Thickness&#xD;&#xA;FROM STATION_POINT AS s3 INNER JOIN&#xD;&#xA; EVENT_RANGE ON s3.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN&#xD;&#xA; STATION_POINT AS s4 ON EVENT_RANGE.STATION_ID_END = s4.STATION_ID AND s3.ROUTE_ID = s4.ROUTE_ID INNER JOIN&#xD;&#xA; ILI_PIPE_LENGTH ON EVENT_RANGE.EVENT_ID = ILI_PIPE_LENGTH.EVENT_ID&#xD;&#xA;WHERE (s3.MEASURE &lt; STATION_POINT_2.MEASURE) AND (s4.MEASURE &gt; STATION_POINT_2.MEASURE) AND (s3.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Measured_Wall_Thickness&#xD;&#xA;FROM EVENT_RANGE INNER JOIN&#xD;&#xA; ILI_DATA ON EVENT_RANGE.EVENT_ID = ILI_DATA.EVENT_ID INNER JOIN&#xD;&#xA; STATION_POINT AS STATION_POINT_2 ON EVENT_RANGE.STATION_ID_BEGIN = STATION_POINT_2.STATION_ID AND &#xD;&#xA; EVENT_RANGE.STATION_ID_END = STATION_POINT_2.STATION_ID&#xD;&#xA;WHERE (ILI_DATA.ILI_INSPECTION_ID = 8)" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="153">
<RelOp AvgRowSize="66" EstimateCPU="0.0239286" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239286" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="60412.5">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
<ColumnReference Column="Expr1035" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="[Expr1033]">
<Identifier>
<ColumnReference Column="Expr1033" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="[Expr1035]">
<Identifier>
<ColumnReference Column="Expr1035" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="66" EstimateCPU="1.00021" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239286" LogicalOp="Left Outer Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="60412.5">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
<ColumnReference Column="Expr1035" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OuterReferences>
<RelOp AvgRowSize="70" EstimateCPU="1.00021" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239286" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="60242.8">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OuterReferences>
<RelOp AvgRowSize="65" EstimateCPU="3.13083" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239286" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="100.874">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<MemoryFractions Input="0.528633" Output="0.891626" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[EVENT_ID]=[Perform_Clustern_PODS_50000_S].[dbo].[ILI_DATA].[EVENT_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="34" EstimateCPU="1.92183" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="42770.6" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="19.0555">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<MemoryFractions Input="0.891626" Output="0.362993" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_END]=[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[STATION_ID] as [STATION_POINT_2].[STATION_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="34" EstimateCPU="0.470634" EstimateIO="12.2061" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="42770.6" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="12.6767">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="1" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]=[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="34" EstimateCPU="0.154483" EstimateIO="4.0972" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="140296" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="4.25168">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[STATION_POINT_2]" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="65" EstimateCPU="1.56144" EstimateIO="76.4446" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239650" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="78.006">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_DATA].[ILI_INSPECTION_ID]=(8.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_INSPECTION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(8.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="12" EstimateCPU="0.000258211" EstimateIO="0.003125" EstimateRebinds="239277" EstimateRewinds="8.04378" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="10" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="60141">
<OutputList>
<ColumnReference Column="Expr1033" />
</OutputList>
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
<RelOp AvgRowSize="12" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="11" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="60078.8">
<OutputList>
<ColumnReference Column="Expr1033" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="20" EstimateCPU="0.00286699" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="12" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="60078.8">
<OutputList>
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1033" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1032" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="ANY([Perform_Clustern_PODS_50000_S].[dbo].[PIPE_SEGMENT].[NOMINAL_DIAMETER_GCL])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="0.171617" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="4777.49" LogicalOp="Inner Join" NodeId="13" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="60002.9">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[STATION_ID] as [s1].[STATION_ID]=[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="21" EstimateCPU="0.232005" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="4850.91" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="55174.5">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_END]=[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[STATION_ID] as [s2].[STATION_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="16" EstimateCPU="0.149813" EstimateIO="5.00518" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="8417.76" LogicalOp="Eager Spool" NodeId="15" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="287.418">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</OutputList>
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="ROUTE_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
<RelOp AvgRowSize="34" EstimateCPU="0.154483" EstimateIO="4.0972" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="140296" LogicalOp="Clustered Index Scan" NodeId="16" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="4.25168">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s2]" />
</IndexScan>
</RelOp>
</Spool>
</RelOp>
<RelOp AvgRowSize="30" EstimateCPU="0.0471295" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="10571.4" LogicalOp="Inner Join" NodeId="17" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="48748.3">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
<ColumnReference Column="Expr1040" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="0.0125595" EstimateIO="0.133495" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="11275" LogicalOp="Clustered Index Scan" NodeId="19" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="332.453">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Index="[PK_PIPE_SEGMENT]" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="298332000" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="20" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="47168.8">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="2" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[PIPE_SEGMENT].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.149813" EstimateIO="5.00518" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="8417.76" LogicalOp="Eager Spool" NodeId="22" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="287.418">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</OutputList>
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="ROUTE_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekPredicate>
<RelOp AvgRowSize="34" EstimateCPU="0.154483" EstimateIO="4.0972" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="140296" LogicalOp="Clustered Index Scan" NodeId="23" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="4.25168">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s1]" />
</IndexScan>
</RelOp>
</Spool>
</RelOp>
</Hash>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1032]&gt;(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1032" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="12" EstimateCPU="0.000258211" EstimateIO="0.003125" EstimateRebinds="239277" EstimateRewinds="8.04378" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="29" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="168.68">
<OutputList>
<ColumnReference Column="Expr1035" />
</OutputList>
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
<RelOp AvgRowSize="12" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="30" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="106.503">
<OutputList>
<ColumnReference Column="Expr1035" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="20" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="31" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="106.499">
<OutputList>
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1034" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="ANY([Perform_Clustern_PODS_50000_S].[dbo].[ILI_PIPE_LENGTH].[MEASURED_WALL_THICKNESS])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="32" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="106.469">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="34" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="55.3994">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OuterReferences>
<RelOp AvgRowSize="30" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="1" LogicalOp="Inner Join" NodeId="36" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.32938">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="7.96E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="38" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0323876">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Index="[PK_ILI_PIPE_LENGTH]" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="39" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="4.18639">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="3" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_PIPE_LENGTH].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="40" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="50.9361">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s4]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [s4].[ROUTE_ID]=[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID] AND [Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [s4].[MEASURE]&gt;[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="41" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="50.9361">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s3]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [s3].[ROUTE_ID]=[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID] AND [Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [s3].[MEASURE]&lt;[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1034]&gt;(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1034" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

-

the plan for delete and 100 data records (bad times):

<?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.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="11.5922" StatementText="DELETE FROM ILI_CLUSTER FROM ILI_CLUSTER where ILI_CLUSTER.ILI_Inspection_ID =22" StatementType="DELETE">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="27">
<MissingIndexes>
<MissingIndexGroup Impact="85.9117">
<MissingIndex Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[ILI_CLUSTER_ID]" ColumnId="57" />
<Column Name="[ILI_CLUSTER_GUID]" ColumnId="58" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="9" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="1" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="11.5922">
<OutputList />
<Assert StartupExpression="false">
<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="11.5922">
<OutputList>
<ColumnReference Column="Expr1014" />
<ColumnReference Column="Expr1015" />
</OutputList>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1015" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1015" />
</ProbeColumn>
<RelOp AvgRowSize="17" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="11.1241">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Column="Expr1014" />
</OutputList>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1014" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1014" />
</ProbeColumn>
<RelOp AvgRowSize="54" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Delete" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="1.16113">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<Update>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[PK_ILI_CLUSTER]" />
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[AK_UK_ILI_CLUSTER_ILI_ILI_CLUS]" />
<RelOp AvgRowSize="16" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.14113">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OutputList>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="25" EstimateCPU="0.037337" EstimateIO="1.08757" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.12491">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OutputList>
<IndexScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[PK_ILI_CLUSTER]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_CLUSTER].[ILI_INSPECTION_ID]=(22.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_INSPECTION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(22.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<RelOp AvgRowSize="54" EstimateCPU="0.172813" EstimateIO="9.65201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="9.82483">
<OutputList />
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[ILI_CLUSTER_ID]=[Perform_Clustern_PODS_100].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_ID] AND [Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[ILI_CLUSTER_GUID]=[Perform_Clustern_PODS_100].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_GUID]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_GUID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.172813" EstimateIO="9.65201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.464606">
<OutputList />
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[ILI_CLUSTER_ID]=[Perform_Clustern_PODS_100].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN NOT [Expr1014] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1015] IS NULL THEN (1) ELSE NULL END END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1015" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

-

the plan for delete and 50.000 data rows

-

<?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.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="39564.9" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="419.029" StatementText="DELETE FROM ILI_CLUSTER FROM ILI_CLUSTER where ILI_CLUSTER.ILI_Inspection_ID =8" StatementType="DELETE">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="30">
<RelOp AvgRowSize="9" EstimateCPU="0.00712168" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39564.9" LogicalOp="Assert" NodeId="1" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="419.029">
<OutputList />
<Assert StartupExpression="false">
<RelOp AvgRowSize="9" EstimateCPU="0.183757" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39564.9" LogicalOp="Left Semi Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="419.022">
<OutputList>
<ColumnReference Column="Expr1015" />
</OutputList>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1015" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1015" />
</ProbeColumn>
<RelOp AvgRowSize="54" EstimateCPU="0.043961" EstimateIO="1.9621" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43961" LogicalOp="Delete" NodeId="3" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="142.747">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<Update WithOrderedPrefetch="true">
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[AK_UK_ILI_CLUSTER_ILI_ILI_CLUS]" />
<RelOp AvgRowSize="54" EstimateCPU="0.00791298" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43961" LogicalOp="Assert" NodeId="5" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="140.741">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="55" EstimateCPU="0.204174" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43961" LogicalOp="Left Semi Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="140.733">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
<ColumnReference Column="Expr1014" />
</OutputList>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1014" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1014" />
</ProbeColumn>
<RelOp AvgRowSize="54" EstimateCPU="0.0488455" EstimateIO="12.2747" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="48845.5" LogicalOp="Delete" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="17.7008">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<Update>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[PK_ILI_CLUSTER]" />
<RelOp AvgRowSize="16" EstimateCPU="0.00488455" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="48845.5" LogicalOp="Top" NodeId="8" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="5.37728">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OutputList>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="25" EstimateCPU="0.202986" EstimateIO="5.0809" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="48845.5" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="5.28389">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[PK_ILI_CLUSTER]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_CLUSTER].[ILI_INSPECTION_ID]=(8.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_INSPECTION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(8.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1.41978" EstimateIO="28.6347" EstimateRebinds="48816.9" EstimateRewinds="27.6594" EstimateRows="16.5831" LogicalOp="Eager Spool" NodeId="11" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="122.828">
<OutputList />
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
<RelOp AvgRowSize="16" EstimateCPU="1.56144" EstimateIO="76.4446" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1419340" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="78.006">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
</IndexScan>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN NOT [Expr1014] IS NULL THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</Update>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1.4196" EstimateIO="73.7834" EstimateRebinds="43960" EstimateRewinds="0" EstimateRows="1" LogicalOp="Eager Spool" NodeId="14" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="276.091">
<OutputList />
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_GUID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_GUID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
<RelOp AvgRowSize="54" EstimateCPU="1.56144" EstimateIO="76.4446" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1419340" LogicalOp="Clustered Index Scan" NodeId="15" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="78.006">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_GUID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
</IndexScan>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN NOT [Expr1015] IS NULL THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1015" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

--

|||

Is this too much?

please help me

|||The query with good times create or use a worktable (Index Spool), is this perhaps the reason why I get so good times. Could this adulterate the times?|||

Have you noticed the following missing index warnings:

For the third plan:

<MissingIndexes>
<MissingIndexGroup Impact="85.9117">
<MissingIndex Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[ILI_CLUSTER_ID]" ColumnId="57" />
<Column Name="[ILI_CLUSTER_GUID]" ColumnId="58" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>

For the first plan:

<MissingIndexes>
<MissingIndexGroup Impact="57.3934">
<MissingIndex Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[ILI_INSPECTION_ID]" ColumnId="61" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[ILI_DATA_ID]" ColumnId="1" />
<Column Name="[EVENT_ID]" ColumnId="3" />
<Column Name="[LENGTH]" ColumnId="21" />
<Column Name="[WIDTH]" ColumnId="22" />
<Column Name="[ORIENTATION_DEG]" ColumnId="23" />
<Column Name="[NOMINAL_WALL_THICKNESS]" ColumnId="33" />
<Column Name="[PIPE_SMYS]" ColumnId="34" />
<Column Name="[B31G_MAOP]" ColumnId="35" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>

Now I wouldn't blindly create these indexes (especially the second one is quite large but it might make sense depending on your specific situation). I would try to create the first one to start with anyway.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||I think that is not the problem, sometimes he messing a index and sometomes he don't missing the index in the database with the bad times. Can there still be present other reasons? Do the execution plan use only the statistics to build the best plan? Are the four plans are very different ?|||

The query optimizer takes into account a lot of factors to create a query plan. Statistics is one the aspects it takes into account.

But nevertheless, bad query may become good queries with the right indexes.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com