Hi,
What's the best way to store a certain kind of information in an SQL-server database:
-having one table with many records (e.g. millions)
or
-having many tables (e.g. thousands) with less records (e.g. thousands)Your question cannot be answered with a simple "statement x is correct" as the answer varies wildly based on the scenario. The best way to find an answer is to do some reading. I suggest you start by reading up on normalisation (www.r937.com/relational.html).|||Ah, the ever popular answer: It depends!
With one pseudo-exception I'll explain in a moment, if you have one single type of thing, then one table is the best way to store it. If you have one thosand similar but slightly different things, then one thousand tables are usually better.
The one place that I'll make an exception to that rule (which is really just a clarification, not a true exception) is that lookups are a different kind of beast. While people sometimes argue that all lookups belong in a single table (this is known as One True Lookup Table or OTLT), lookups really belong in separate tables.
If you have 96 kinds of lookup items, those are really 96 quite different things. If you can put hat sizes and truck colors into a lookup table, but you can't exchange a hat size for a truck color (no one I know wears a "Sunburnt Orange" size hat or drives a 6 7/8 colored truck), then the hat sizes are a different thing than the truck colors. Because they are different things, they ought to be in a different table.
-PatP
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
performance question
Why does Query1 below have a Scan with 21 Logical Reads for 31 records
returned (a small percentage of Logicalreads), when Query 2 has no Scan,
only returns 6 records, and has 14 Logical reads. (a higher percentage)
In other words, the more data, the fewer Logical reads but no Scan. The less
data, higher reads but a scan. What gives?
SQL2K sp3a
TIA, ChrisRwhat query? what data types are the columns?
--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ChrisR" <noemail@.bla.com> wrote in message
news:OLWXZnMiFHA.3672@.TK2MSFTNGP10.phx.gbl...
> Why does Query1 below have a Scan with 21 Logical Reads for 31 records
> returned (a small percentage of Logicalreads), when Query 2 has no Scan,
> only returns 6 records, and has 14 Logical reads. (a higher percentage)
> In other words, the more data, the fewer Logical reads but no Scan. The
> less data, higher reads but a scan. What gives?
> SQL2K sp3a
> TIA, ChrisR
>|||The scan on a clustered or nonclustered index at the leaf level is pretty
simple since it goes from leaf page to leaf page in order. It can read
relatively few pages to get a lot of rows that way. A seek has to traverse
the B-Tree from root to leaf level for each row returned. Depending on the
size of the index tree it can read a minimum of 2 pages per row seeked.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OLWXZnMiFHA.3672@.TK2MSFTNGP10.phx.gbl...
> Why does Query1 below have a Scan with 21 Logical Reads for 31 records
> returned (a small percentage of Logicalreads), when Query 2 has no Scan,
> only returns 6 records, and has 14 Logical reads. (a higher percentage)
> In other words, the more data, the fewer Logical reads but no Scan. The
> less data, higher reads but a scan. What gives?
> SQL2K sp3a
> TIA, ChrisR
>sql
returned (a small percentage of Logicalreads), when Query 2 has no Scan,
only returns 6 records, and has 14 Logical reads. (a higher percentage)
In other words, the more data, the fewer Logical reads but no Scan. The less
data, higher reads but a scan. What gives?
SQL2K sp3a
TIA, ChrisRwhat query? what data types are the columns?
--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ChrisR" <noemail@.bla.com> wrote in message
news:OLWXZnMiFHA.3672@.TK2MSFTNGP10.phx.gbl...
> Why does Query1 below have a Scan with 21 Logical Reads for 31 records
> returned (a small percentage of Logicalreads), when Query 2 has no Scan,
> only returns 6 records, and has 14 Logical reads. (a higher percentage)
> In other words, the more data, the fewer Logical reads but no Scan. The
> less data, higher reads but a scan. What gives?
> SQL2K sp3a
> TIA, ChrisR
>|||The scan on a clustered or nonclustered index at the leaf level is pretty
simple since it goes from leaf page to leaf page in order. It can read
relatively few pages to get a lot of rows that way. A seek has to traverse
the B-Tree from root to leaf level for each row returned. Depending on the
size of the index tree it can read a minimum of 2 pages per row seeked.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OLWXZnMiFHA.3672@.TK2MSFTNGP10.phx.gbl...
> Why does Query1 below have a Scan with 21 Logical Reads for 31 records
> returned (a small percentage of Logicalreads), when Query 2 has no Scan,
> only returns 6 records, and has 14 Logical reads. (a higher percentage)
> In other words, the more data, the fewer Logical reads but no Scan. The
> less data, higher reads but a scan. What gives?
> SQL2K sp3a
> TIA, ChrisR
>sql
Wednesday, March 21, 2012
Performance Problems On Recursive Table
I have a table that has a parent-child relation to itself. (see SQL at end)
Basically, records of type 0 are related to type 1, and type 1 to type 2.
This table has about 2 million records right now. When I do this:
INNER JOIN
ExpSrvLog I ON E.ExpSrvID = I.ExpSrvID AND I.ItemType = 2
LEFT OUTER JOIN
ExpSrvLog B ON I.LogID = B.ParentID AND B.ItemType = 1
It seems to consume an inordinate amount of time. Any way to optimize this
behavior? Thanks.
CREATE TABLE [ExpSrvLog] (
[LogID] [bigint] IDENTITY (1, 1) NOT NULL ,
[ExpSrvID] [int] NOT NULL ,
[CalendarDate] [smalldatetime] NOT NULL ,
[ItemType] [tinyint] NOT NULL ,
[VendorInvoiceID] [int] NULL ,
[ClientInvoiceID] [int] NULL ,
[PaymentID] [int] NULL ,
[BillingAmount] [money] NULL ,
[ActualQty] [decimal](8, 2) NULL ,
[ActualRate] [money] NULL ,
[ParentID] [bigint] NULL ,
CONSTRAINT [PK_ExpSrvLog] PRIMARY KEY CLUSTERED
(
[LogID] DESC
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_ExpSrvLog_ClientInvoice] FOREIGN KEY
(
[ClientInvoiceID]
) REFERENCES [ClientInvoice] (
[InvoiceID]
),
CONSTRAINT [FK_ExpSrvLog_ExpSrvLog] FOREIGN KEY
(
[ParentID]
) REFERENCES [ExpSrvLog] (
[LogID]
),
CONSTRAINT [FK_ExpSrvLog_VendorInvoice] FOREIGN KEY
(
[VendorInvoiceID]
) REFERENCES [VendorInvoice] (
[InvoiceID]
)
) ON [PRIMARY]
ENDApart from the primary key, are there any other indexes on that table?
Foreign key columns should at least be indexed.
ML|||"ML" wrote:
> Apart from the primary key, are there any other indexes on that table?
Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FKs.
)
I've also tried an index over ParentID and LogID, to no help.
- alphadog|||Paul, While recursive joins are elegant, they are notoriously slow on
recursive tables with > 1 million rows. You might want try to use mutiples
queries and(or) tables to accomplish your task instead of the recursive join
s.
"Paul Tiseo" wrote:
> "ML" wrote:
> Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
> VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FK
s.)
> I've also tried an index over ParentID and LogID, to no help.
> - alphadog|||Really? Damn. It works so well that way. Oh well, thanks for the info, Frank
.
So, what are all my options? Physically splitting the table is something I
have planned, but at this point in developement is not an option. I guess
it'll have to be temp tables.
"frank chang" wrote:
> Paul, While recursive joins are elegant, they are notoriously slow on
> recursive tables with > 1 million rows. You might want try to use mutiples
> queries and(or) tables to accomplish your task instead of the recursive jo
ins.
> "Paul Tiseo" wrote:
>|||Maybe this example can help you find a way to navigate the
ancestor/descendant axes in your hierarchy:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||>> I have a table that has a parent-child relation to itself. (see SQL at end) Basical
ly, records [sic] of type 0 are related to type 1, and type 1 to type 2. This table h
as about 2 million records [sic] right now. When I do this: <<
No relational key, IDENTITY, too many NULL-able columns, money and
bigint proprierary datatypes and you don't know that rows and records
are not the same. .And there are not specs.
behavior? <<
Change the DDL. Does this hierarchy only go down three levels? Can
level 0 have more than one level 1 subordinate? Can a level 1 have
more than one level 2 subordinate? If I assume not, then:
CREATE TABLE ExpsrvLog
(expsrv_id INTEGER NOT NULL,
hierarchy_level INTEGER DEFAULT 0 NOT NULL
CHECK (hierarchy_level IN (0, 1, 2)),
payment_date DATETIME NOT NULL,
vendor_invoice_nbr INTEGER NOT NULL,
REFERENCES VendorInvoices (invoice_nbr)
ON UPDATE CASCADE,
client_invoice_nbr INTEGER NOT NULL
REFERENCES ClientInvoices (invoice_nbr)
ON UPDATE CASCADE,
payment_nbr INTEGER NOT NULL,
billing_amount DECIMAL(8,2) NOT NULL,
actual_qty DECIMAL(8,2) NOT NULL,
actual_rate DECIMAL(8,2) NULL,
PRIMARY KEY (expsrv_id, hierarchy_level));
If the asumption was wrong, we can move onto the nested sets model.
You might want to get a copy of TREES & HIERARCHIES IN SQL along with a
basic data modeling book.|||"--CELKO--" wrote:
> No relational key,
Of what type? I have a primary key (see first post), an alternate key (not
in the original DDL, but basically CREATE UNIQUE INDEX AK_ExpSrvLog ON
dbo.ExpSrvLog([ExpSrvID], [CalendarDate] DESC , [ItemType]) ) and some
primary-foreign key relationships. What exactly is missing? Do you mean a
natural key? If so, it's the AK that I did not include.
> IDENTITY
Why? How does IDENTITY affect my performance question?
> too many NULL-able columns,
The Real World intrudes into the World of Relational Model Puritans
sometimes. <shrug> Again, how do the NULLable columns affect my performance
problem?
> money and bigint proprierary datatypes
Not a problem for this project.
> and you don't know that rows and records are not the same.
Do you know what they say about "ASS-U-ME"? I use common idioms. Forgive me
for not abiding by your stricter one.
> And there are not specs.
I'm sorry, but I don't understand this comment. What specs do you need?
> Change the DDL. Does this hierarchy only go down three levels? Can
> level 0 have more than one level 1 subordinate? Can a level 1 have
> more than one level 2 subordinate? If I assume not, then:
Yes (three and only three levels), yes and yes.
> CREATE TABLE ExpsrvLog
> (expsrv_id INTEGER NOT NULL,
> hierarchy_level INTEGER DEFAULT 0 NOT NULL
> CHECK (hierarchy_level IN (0, 1, 2)),
> payment_date DATETIME NOT NULL,
> vendor_invoice_nbr INTEGER NOT NULL,
> REFERENCES VendorInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> client_invoice_nbr INTEGER NOT NULL
> REFERENCES ClientInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> payment_nbr INTEGER NOT NULL,
> billing_amount DECIMAL(8,2) NOT NULL,
> actual_qty DECIMAL(8,2) NOT NULL,
> actual_rate DECIMAL(8,2) NULL,
> PRIMARY KEY (expsrv_id, hierarchy_level));
> If the asumption was wrong, we can move onto the nested sets model.
So, you changed the structure of the table in a way that doesn't address my
original problem (in fact you seem to have removed the recursive
relationship) but makes it more purist-friendly in terms of datatypes and
nullability. Your "SQL standardization" and "Relational Model" efforts are
appreciated (although your zeal blinded you to my actual question) but it's
not a solution to my immediate problem.
I don't need to know *how* to model or query the hierarchy. I need to know
*why* it isn't performing properly when I use the self-join.
Thanks.sql
Basically, records of type 0 are related to type 1, and type 1 to type 2.
This table has about 2 million records right now. When I do this:
INNER JOIN
ExpSrvLog I ON E.ExpSrvID = I.ExpSrvID AND I.ItemType = 2
LEFT OUTER JOIN
ExpSrvLog B ON I.LogID = B.ParentID AND B.ItemType = 1
It seems to consume an inordinate amount of time. Any way to optimize this
behavior? Thanks.
CREATE TABLE [ExpSrvLog] (
[LogID] [bigint] IDENTITY (1, 1) NOT NULL ,
[ExpSrvID] [int] NOT NULL ,
[CalendarDate] [smalldatetime] NOT NULL ,
[ItemType] [tinyint] NOT NULL ,
[VendorInvoiceID] [int] NULL ,
[ClientInvoiceID] [int] NULL ,
[PaymentID] [int] NULL ,
[BillingAmount] [money] NULL ,
[ActualQty] [decimal](8, 2) NULL ,
[ActualRate] [money] NULL ,
[ParentID] [bigint] NULL ,
CONSTRAINT [PK_ExpSrvLog] PRIMARY KEY CLUSTERED
(
[LogID] DESC
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_ExpSrvLog_ClientInvoice] FOREIGN KEY
(
[ClientInvoiceID]
) REFERENCES [ClientInvoice] (
[InvoiceID]
),
CONSTRAINT [FK_ExpSrvLog_ExpSrvLog] FOREIGN KEY
(
[ParentID]
) REFERENCES [ExpSrvLog] (
[LogID]
),
CONSTRAINT [FK_ExpSrvLog_VendorInvoice] FOREIGN KEY
(
[VendorInvoiceID]
) REFERENCES [VendorInvoice] (
[InvoiceID]
)
) ON [PRIMARY]
ENDApart from the primary key, are there any other indexes on that table?
Foreign key columns should at least be indexed.
ML|||"ML" wrote:
> Apart from the primary key, are there any other indexes on that table?
Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FKs.
)
I've also tried an index over ParentID and LogID, to no help.
- alphadog|||Paul, While recursive joins are elegant, they are notoriously slow on
recursive tables with > 1 million rows. You might want try to use mutiples
queries and(or) tables to accomplish your task instead of the recursive join
s.
"Paul Tiseo" wrote:
> "ML" wrote:
> Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
> VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FK
s.)
> I've also tried an index over ParentID and LogID, to no help.
> - alphadog|||Really? Damn. It works so well that way. Oh well, thanks for the info, Frank
.
So, what are all my options? Physically splitting the table is something I
have planned, but at this point in developement is not an option. I guess
it'll have to be temp tables.
"frank chang" wrote:
> Paul, While recursive joins are elegant, they are notoriously slow on
> recursive tables with > 1 million rows. You might want try to use mutiples
> queries and(or) tables to accomplish your task instead of the recursive jo
ins.
> "Paul Tiseo" wrote:
>|||Maybe this example can help you find a way to navigate the
ancestor/descendant axes in your hierarchy:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||>> I have a table that has a parent-child relation to itself. (see SQL at end) Basical
ly, records [sic] of type 0 are related to type 1, and type 1 to type 2. This table h
as about 2 million records [sic] right now. When I do this: <<
No relational key, IDENTITY, too many NULL-able columns, money and
bigint proprierary datatypes and you don't know that rows and records
are not the same. .And there are not specs.
behavior? <<
Change the DDL. Does this hierarchy only go down three levels? Can
level 0 have more than one level 1 subordinate? Can a level 1 have
more than one level 2 subordinate? If I assume not, then:
CREATE TABLE ExpsrvLog
(expsrv_id INTEGER NOT NULL,
hierarchy_level INTEGER DEFAULT 0 NOT NULL
CHECK (hierarchy_level IN (0, 1, 2)),
payment_date DATETIME NOT NULL,
vendor_invoice_nbr INTEGER NOT NULL,
REFERENCES VendorInvoices (invoice_nbr)
ON UPDATE CASCADE,
client_invoice_nbr INTEGER NOT NULL
REFERENCES ClientInvoices (invoice_nbr)
ON UPDATE CASCADE,
payment_nbr INTEGER NOT NULL,
billing_amount DECIMAL(8,2) NOT NULL,
actual_qty DECIMAL(8,2) NOT NULL,
actual_rate DECIMAL(8,2) NULL,
PRIMARY KEY (expsrv_id, hierarchy_level));
If the asumption was wrong, we can move onto the nested sets model.
You might want to get a copy of TREES & HIERARCHIES IN SQL along with a
basic data modeling book.|||"--CELKO--" wrote:
> No relational key,
Of what type? I have a primary key (see first post), an alternate key (not
in the original DDL, but basically CREATE UNIQUE INDEX AK_ExpSrvLog ON
dbo.ExpSrvLog([ExpSrvID], [CalendarDate] DESC , [ItemType]) ) and some
primary-foreign key relationships. What exactly is missing? Do you mean a
natural key? If so, it's the AK that I did not include.
> IDENTITY
Why? How does IDENTITY affect my performance question?
> too many NULL-able columns,
The Real World intrudes into the World of Relational Model Puritans
sometimes. <shrug> Again, how do the NULLable columns affect my performance
problem?
> money and bigint proprierary datatypes
Not a problem for this project.
> and you don't know that rows and records are not the same.
Do you know what they say about "ASS-U-ME"? I use common idioms. Forgive me
for not abiding by your stricter one.
> And there are not specs.
I'm sorry, but I don't understand this comment. What specs do you need?
> Change the DDL. Does this hierarchy only go down three levels? Can
> level 0 have more than one level 1 subordinate? Can a level 1 have
> more than one level 2 subordinate? If I assume not, then:
Yes (three and only three levels), yes and yes.
> CREATE TABLE ExpsrvLog
> (expsrv_id INTEGER NOT NULL,
> hierarchy_level INTEGER DEFAULT 0 NOT NULL
> CHECK (hierarchy_level IN (0, 1, 2)),
> payment_date DATETIME NOT NULL,
> vendor_invoice_nbr INTEGER NOT NULL,
> REFERENCES VendorInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> client_invoice_nbr INTEGER NOT NULL
> REFERENCES ClientInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> payment_nbr INTEGER NOT NULL,
> billing_amount DECIMAL(8,2) NOT NULL,
> actual_qty DECIMAL(8,2) NOT NULL,
> actual_rate DECIMAL(8,2) NULL,
> PRIMARY KEY (expsrv_id, hierarchy_level));
> If the asumption was wrong, we can move onto the nested sets model.
So, you changed the structure of the table in a way that doesn't address my
original problem (in fact you seem to have removed the recursive
relationship) but makes it more purist-friendly in terms of datatypes and
nullability. Your "SQL standardization" and "Relational Model" efforts are
appreciated (although your zeal blinded you to my actual question) but it's
not a solution to my immediate problem.
I don't need to know *how* to model or query the hierarchy. I need to know
*why* it isn't performing properly when I use the self-join.
Thanks.sql
Performance Problems in SQL Server 2000
Hello All,
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select
*
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows?
The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA,
did you try both text and grid
mode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTN
GP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ selec
t *
> from myTable ] query on the same machine where DB is installed. Also, I ha
ve
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>|||Dear Irfan,
Please check your statistics using "DBCC SHOW_STATISTICS" if your table stat
ictics are not update properly than update using "update STATISTICS " or "sp
_updatestats".Hope it will help u .
Faheem
NETSOL
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select
*
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows?
The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA,
did you try both text and grid
mode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTN
GP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ selec
t *
> from myTable ] query on the same machine where DB is installed. Also, I ha
ve
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>|||Dear Irfan,
Please check your statistics using "DBCC SHOW_STATISTICS" if your table stat
ictics are not update properly than update using "update STATISTICS " or "sp
_updatestats".Hope it will help u .
Faheem
NETSOL
Performance Problems in SQL Server 2000
Hello All,
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select *
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.
Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows? The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA, did you try both text and grid
mode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ select *
> from myTable ] query on the same machine where DB is installed. Also, I have
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>
|||Dear Irfan,
Please check your statistics using "DBCC SHOW_STATISTICS" if your table statictics are not update properly than update using "update STATISTICS " or "sp_updatestats".Hope it will help u .
Faheem
NETSOL
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select *
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.
Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows? The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA, did you try both text and grid
mode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ select *
> from myTable ] query on the same machine where DB is installed. Also, I have
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>
|||Dear Irfan,
Please check your statistics using "DBCC SHOW_STATISTICS" if your table statictics are not update properly than update using "update STATISTICS " or "sp_updatestats".Hope it will help u .
Faheem
NETSOL
Performance Problems in SQL Server 2000
Hello All,
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select *
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows? The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA, did you try both text and grid
mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ select *
> from myTable ] query on the same machine where DB is installed. Also, I have
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>|||Dear Irfan
Please check your statistics using "DBCC SHOW_STATISTICS" if your table statictics are not update properly than update using "update STATISTICS " or "sp_updatestats".Hope it will help u
Faheem
NETSOL
I am having acute problems with SQL Server 2000 having a single FLAT table
with more than 6,00,000 records.
It is taking almost nine minutes to get all records when I run [ select *
from myTable ] query on the same machine where DB is installed. Also, I have
created a Clustered index over a field with no Primary Key Constraints.
Any help in this context will be highly appreciated.
Thanks in Advance.
Irfan Zaidi.Is that 6,000,000 or 600,000? Why would you want to retrieve that many rows? The time is possibly in the
client processing and not the server. Did you use QA or OSQL? If you use QA, did you try both text and grid
mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Irfan Zaidi" <irfan.zaidi@.pk.softechww.com> wrote in message news:eTbhCPsJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> I am having acute problems with SQL Server 2000 having a single FLAT table
> with more than 6,00,000 records.
> It is taking almost nine minutes to get all records when I run [ select *
> from myTable ] query on the same machine where DB is installed. Also, I have
> created a Clustered index over a field with no Primary Key Constraints.
> Any help in this context will be highly appreciated.
> Thanks in Advance.
> Irfan Zaidi.
>|||Dear Irfan
Please check your statistics using "DBCC SHOW_STATISTICS" if your table statictics are not update properly than update using "update STATISTICS " or "sp_updatestats".Hope it will help u
Faheem
NETSOL
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,
(SELECT PIPE_SEGMENT.NOMINAL_DIAMETER_GCL
FROM STATION_POINT as s1 INNER JOIN
 EVENT_RANGE ON s1.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN
 STATION_POINT AS s2 ON EVENT_RANGE.STATION_ID_END = s2.STATION_ID AND 
 s1.ROUTE_ID = s2.ROUTE_ID INNER JOIN
 PIPE_SEGMENT ON EVENT_RANGE.EVENT_ID = PIPE_SEGMENT.EVENT_ID
WHERE (s1.MEASURE < STATION_POINT_2.MEASURE) AND (s2.MEASURE > STATION_POINT_2.MEASURE) AND (s1.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Nominal_Diameter,
(SELECT ILI_PIPE_LENGTH.Measured_Wall_Thickness
FROM STATION_POINT AS s3 INNER JOIN
 EVENT_RANGE ON s3.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN
 STATION_POINT AS s4 ON EVENT_RANGE.STATION_ID_END = s4.STATION_ID AND s3.ROUTE_ID = s4.ROUTE_ID INNER JOIN
 ILI_PIPE_LENGTH ON EVENT_RANGE.EVENT_ID = ILI_PIPE_LENGTH.EVENT_ID
WHERE (s3.MEASURE < STATION_POINT_2.MEASURE) AND (s4.MEASURE > STATION_POINT_2.MEASURE) AND (s3.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Measured_Wall_Thickness
FROM EVENT_RANGE INNER JOIN
 ILI_DATA ON EVENT_RANGE.EVENT_ID = ILI_DATA.EVENT_ID INNER JOIN
 STATION_POINT AS STATION_POINT_2 ON EVENT_RANGE.STATION_ID_BEGIN = STATION_POINT_2.STATION_ID AND 
 EVENT_RANGE.STATION_ID_END = STATION_POINT_2.STATION_ID
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]>(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]<[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]>[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]>(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,
(SELECT PIPE_SEGMENT.NOMINAL_DIAMETER_GCL
FROM STATION_POINT as s1 INNER JOIN
 EVENT_RANGE ON s1.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN
 STATION_POINT AS s2 ON EVENT_RANGE.STATION_ID_END = s2.STATION_ID AND 
 s1.ROUTE_ID = s2.ROUTE_ID INNER JOIN
 PIPE_SEGMENT ON EVENT_RANGE.EVENT_ID = PIPE_SEGMENT.EVENT_ID
WHERE (s1.MEASURE < STATION_POINT_2.MEASURE) AND (s2.MEASURE > STATION_POINT_2.MEASURE) AND (s1.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Nominal_Diameter,
(SELECT ILI_PIPE_LENGTH.Measured_Wall_Thickness
FROM STATION_POINT AS s3 INNER JOIN
 EVENT_RANGE ON s3.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN
 STATION_POINT AS s4 ON EVENT_RANGE.STATION_ID_END = s4.STATION_ID AND s3.ROUTE_ID = s4.ROUTE_ID INNER JOIN
 ILI_PIPE_LENGTH ON EVENT_RANGE.EVENT_ID = ILI_PIPE_LENGTH.EVENT_ID
WHERE (s3.MEASURE < STATION_POINT_2.MEASURE) AND (s4.MEASURE > STATION_POINT_2.MEASURE) AND (s3.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Measured_Wall_Thickness
FROM EVENT_RANGE INNER JOIN
 ILI_DATA ON EVENT_RANGE.EVENT_ID = ILI_DATA.EVENT_ID INNER JOIN
 STATION_POINT AS STATION_POINT_2 ON EVENT_RANGE.STATION_ID_BEGIN = STATION_POINT_2.STATION_ID AND 
 EVENT_RANGE.STATION_ID_END = STATION_POINT_2.STATION_ID
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]>(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]>[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]<[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]>(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
Subscribe to:
Posts (Atom)