Performance Question

Obviously you do not want to have an open connection to a database
when your program is waiting for user input but if you are doing a
bunch of processing after a user request is it better to open and
close connections when you need access to the database or open once,
use multiple times and then close when you know you will not need
access again?

I guess what I am asking is, what is more important? Minimizing your
concurrent connections to a database or the performance hit of
repeated open and closing a connection.

I know there probably isn't one correct answer but what should I take
into consideration?

John.
The general rule of thumb here is "open late, close early".

Open your connection just before you need to use it, and close it as soon as possible. In my opinion, you would do this each time you need a connection. The reason for this is that by default, your connections are pooled. Normally, when you open a connection that uses an identical connection string as one that has already been opened and closed, you application will grab that connection again. Therefor, the connection does not need to be initialized and authenticated to SQL Server again.

Generally, I design my methods to perform as few database operations as possible with a goal of creating methods that are loosely coupled and highly cohesive. I create and open the connection within the method, use it, then close it. I have analyzed the connection performance with Performance Monitor and this seems to work well. Sometimes it is necessary to loop through some data, and process some database operation one record at a time. In this case I will usually open the connection just before the loop, and close it immediately after.

Ultimately, you may need to experiment a little for yourself. Every situation is different, and calls for different design considerations. You may be interested in having a look at theData Access Application Block from Microsoft for somebest practices.

Hope this is helpful.
|||I agree with NetProfit. I would like to add a couple of points to think on. Its not just for Db hits either, but you should also try to avoid chatty interface to the DB as well, to cut down on network roundtrips. I would also say that generally its better to get the whole result set back, close the connection ASAP and then work on the data. I've seen plenty of solutions that mis-use the datareader.|||Thanks. I had forgetten about connection pooling. I am looking forward to reading the Data Access Application Block article too.

John.

Performance question

I have just upsized a FoxPro DB to MSDE.
I use a VB 6 program with ADO to access the DB.
I put the .EXE file on the server with a shortcut to it on each of the
workstations.
Performance is much worse then the FoxPro in some of the tasks.
In my investigations I found that if I put the .EXE on each workstation
performance improved 10 fold.
Can anyone explain why this happened? I would like to only have to have one
version of the .EXE floating around.
Thanks,
Darwin C. Weyh
swdev at superiorheattreat dot com
And for the spammers: dweyh@.weyh.net
The first question I would pose is:
Are you using a client side or server side cursor in your calls to the
database? Also, is your database on a network server? If so, why are you
using MSDE, why not a full blown version of SQL Server? I would guess it
would be possible to place individual instances of MSDE on local machines
which were in essence copies of each other, but it sounds convoluted.
Jamie
"Darwin Weyh" wrote:

> I have just upsized a FoxPro DB to MSDE.
> I use a VB 6 program with ADO to access the DB.
> I put the .EXE file on the server with a shortcut to it on each of the
> workstations.
> Performance is much worse then the FoxPro in some of the tasks.
> In my investigations I found that if I put the .EXE on each workstation
> performance improved 10 fold.
> Can anyone explain why this happened? I would like to only have to have one
> version of the .EXE floating around.
> Thanks,
> --
> Darwin C. Weyh
> swdev at superiorheattreat dot com
> And for the spammers: dweyh@.weyh.net
>
>
|||hi Darwin,
Darwin Weyh wrote:
> I have just upsized a FoxPro DB to MSDE.
> I use a VB 6 program with ADO to access the DB.
> I put the .EXE file on the server with a shortcut to it on each of the
> workstations.
> Performance is much worse then the FoxPro in some of the tasks.
> In my investigations I found that if I put the .EXE on each
> workstation performance improved 10 fold.
> Can anyone explain why this happened? I would like to only have to
> have one version of the .EXE floating around.
> Thanks,
you are actually killing your lan with a lot of unnecessary traffic
(application and data) and using the OSs as a terminal server...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The MSDE resides on a SBS 2003 machine as did the file served .exe
I'm using server side cursors.
SQL Server cost more.
My original configuration used VFP files on the SBS 2003 server as did the
file served .exe.
And I used client side cursors.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:EF072B21-7200-4D6E-B0A1-430EF776F661@.microsoft.com...[vbcol=seagreen]
> The first question I would pose is:
> Are you using a client side or server side cursor in your calls to the
> database? Also, is your database on a network server? If so, why are you
> using MSDE, why not a full blown version of SQL Server? I would guess it
> would be possible to place individual instances of MSDE on local machines
> which were in essence copies of each other, but it sounds convoluted.
> Jamie
> "Darwin Weyh" wrote:

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