I have the following SQL query:
select *
from NETWORK n
inner join vwNETWORK_KEYMSTN km on n.Network_ID = km.Network_ID
inner join vwAPPROVAL_LATEST a on n.Network_ID = a.Network_ID
inner join APPROVAL_VINTAGE av on a.Approval_ID = av.Approval_ID and km.Milestone_Type_ID = av.Milestone_Type_ID
inner join NETWORK_MILESTONE m on A.Approval_ID = m.Approval_ID and km.Milestone_Type_ID = m.Milestone_Type_ID
inner join REF_MILESTONE rm on km.Milestone_Type_ID = rm.Milestone_Type_ID
inner join NETWORK_STATUS s on n.Network_ID = s.Network_ID --and s.Status_Type_ID = 2
--where rm.Status_Type_ID = s.Status_Type_ID
This query returns in about 6s. The last table, NETWORK_STATUS, causes my result set to split out. That is, n.Network_ID would be unique before adding that last table NETWORK_STATUS to the join.
Adding the join clause that is currently commented out will return a unique set of Network_ID again. The commented out join will a result set in fast time, ie, < 10s.
However, what I truly want is the where clause that is commented out, or I could put it as part of the join. A MILESTONE_type is associated with only one STATUS_type. Putting this part in, which is seemingly a trivial addition to the query, causes a massive explosion in run time... I kill the query after a minute, but it could be even greater than a 6x factor increase in run time.
My question to this forum is why that might be the case, and more importantly, how do I tell SQL Server that this really isn't a hard where clause to execute. At most, the where clause should return in < 20s... it could return the full set with duplicates (~6s), and then 2nd pass simply trim out the rows that don't satisfy the where clause.
Execution Plan: here is the problem line:
--Index Scan(OBJECT:([CSDConstruction].[dbo].[NETWORK_STATUS].[IX_NETWORK_STATUS_Status_Type_ID] AS [s]), WHERE:(PROBE([Opt_Bitmap1070],[CSDConstruction].[dbo].[NETWORK_STATUS].[Status_Type_ID] as [s].[Status_Type_ID],N'[IN ROW]')))
Here is the full execution plan:
|--Compute Scalar(DEFINE:([Expr1029]=CASE WHEN [CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_DT] as [m1].[Milestone_DT]<>(-1) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [cprd].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))>=isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:EQ%' OR [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:IP' THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))<isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [nisr].[Milestone_Type_ID] ELSE [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [unknown].[Milestone_Type_ID] END END END END))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, WHERE:(CASE WHEN [CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_DT] as [m1].[Milestone_DT]<>(-1) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [cprd].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))>=isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:EQ%' OR [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:IP' THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))<isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [nisr].[Milestone_Type_ID] ELSE [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [unknown].[Milestone_Type_ID] END END END END=[CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [rm].[Milestone_Type_ID] AND CASE WHEN [CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_DT] as [m1].[Milestone_DT]<>(-1) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [cprd].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))>=isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:EQ%' OR [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:IP' THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))<isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [nisr].[Milestone_Type_ID] ELSE [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [unknown].[Milestone_Type_ID] END END END END=[CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_Type_ID] as [m].[Milestone_Type_ID] AND CASE WHEN [CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_DT] as [m1].[Milestone_DT]<>(-1) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [cprd].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))>=isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:EQ%' OR [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:IP' THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))<isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [nisr].[Milestone_Type_ID] ELSE [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [unknown].[Milestone_Type_ID] END END END END=[CSDConstruction].[dbo].[APPROVAL_VINTAGE].[Milestone_Type_ID] as [av].[Milestone_Type_ID]))
|--Nested Loops(Inner Join)
| |--Nested Loops(Inner Join)
| | |--Hash Match(Inner Join, HASH:([cprd].[Milestone_Type_ID])=([m1].[Milestone_Type_ID]))
| | | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | | |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [cprd]), WHERE:([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [cprd].[Milestone_NM]='CPRD'))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([m1].[Milestone_ID], [Expr1078]) WITH UNORDERED PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[Approval_ID], [Expr1077]) WITH UNORDERED PREFETCH)
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([m].[Milestone_ID], [Expr1076]) WITH ORDERED PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[Approval_ID]))
| | | | | | |--Sort(ORDER BY:([b].[Approval_ID] ASC))
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [ra].[Approval_Type_ID], [s].[Network_ID]))
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[Approval_ID]))
| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[Approval_Type_ID], [Expr1034], [s].[Network_ID]))
| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
| | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
| | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[Approval_Type_ID]))
| | | | | | | | | | | | |--Stream Aggregate(GROUP BY:([s].[Network_Status_ID], [rm].[Milestone_Type_ID], [a].[Approval_Type_ID], [a].[Approval_Type_ID]) DEFINE:([Expr1008]=MAX([CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_Version_NUM] as [a].[Approval_Version_NUM]), [Expr1015]=ANY([Expr1015]), [Expr1016]=ANY([Expr1016]), [Expr1034]=ANY([Expr1034]), [rm].[Milestone_NM]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [rm].[Milestone_NM]), [rm].[Status_Type_ID]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Status_Type_ID] as [rm].[Status_Type_ID]), [s].[Network_ID]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]), [s].[Status_Type_ID]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Status_Type_ID] as [rm].[Status_Type_ID]), [s].[Status_DT]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Status_DT] as [s].[Status_DT]), [s].[Current_IND]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Current_IND] as [s].[Current_IND])))
| | | | | | | | | | | | | |--Sort(ORDER BY:([s].[Network_Status_ID] ASC, [rm].[Milestone_Type_ID] ASC, [a].[Approval_Type_ID] ASC, [a].[Approval_Type_ID] ASC))
| | | | | | | | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([s].[Network_Status_ID], [rm].[Milestone_Type_ID], [a].[Approval_Type_ID], [a].[Approval_Type_ID]))
| | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
| | | | | | | | | | | | | |--Stream Aggregate(GROUP BY:([s].[Network_Status_ID], [rm].[Milestone_Type_ID], [a].[Approval_Type_ID]) DEFINE:([Expr1034]=MAX([CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_Version_NUM] as [a].[Approval_Version_NUM]), [Expr1015]=ANY([Expr1015]), [Expr1016]=ANY([Expr1016]), [rm].[Milestone_NM]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [rm].[Milestone_NM]), [rm].[Status_Type_ID]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Status_Type_ID] as [rm].[Status_Type_ID]), [s].[Network_ID]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]), [s].[Status_Type_ID]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Status_Type_ID] as [rm].[Status_Type_ID]), [s].[Status_DT]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Status_DT] as [s].[Status_DT]), [s].[Current_IND]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Current_IND] as [s].[Current_IND])))
| | | | | | | | | | | | | | |--Sort(ORDER BY:([s].[Network_Status_ID] ASC, [rm].[Milestone_Type_ID] ASC, [a].[Approval_Type_ID] ASC))
| | | | | | | | | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([s].[Network_Status_ID], [rm].[Milestone_Type_ID], [a].[Approval_Type_ID]))
| | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
| | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
| | | | | | | | | | | | | | | |--Hash Match(Inner Join, HASH:([rm].[Status_Type_ID])=([s].[Status_Type_ID]))
| | | | | | | | | | | | | | | | |--Bitmap(HASH:([rm].[Status_Type_ID]), DEFINE:([Opt_Bitmap1070]))
| | | | | | | | | | | | | | | | | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [rm]))
| | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_Status_ID]))
| | | | | | | | | | | | | | | | |--Parallelism(Repartition Streams, RoundRobin Partitioning)
| | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([CSDConstruction].[dbo].[NETWORK_STATUS].[IX_NETWORK_STATUS_Status_Type_ID] AS [s]), WHERE:(PROBE([Opt_Bitmap1070],[CSDConstruction].[dbo].[NETWORK_STATUS].[Status_Type_ID] as [s].[Status_Type_ID],N'[IN ROW]')))
| | | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_STATUS].[PK_NETWORK_STATUS] AS [s]), SEEK:([s].[Network_Status_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_Status_ID] as [s].[Network_Status_ID]) LOOKUP ORDERED FORWARD)
| | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1015]=CASE WHEN [Expr1072]=(0) THEN NULL ELSE [Expr1073] END, [Expr1016]=CASE WHEN [Expr1074]=(0) THEN NULL ELSE [Expr1075] END))
| | | | | | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1072]=COUNT_BIG(CASE WHEN [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_IP%' OR [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_Ot%' THEN [CSDConstruction].[dbo].[NETWORK_EXPENSE].[Expense_AMT] as [e].[Expense_AMT] ELSE NULL END), [Expr1073]=SUM(CASE WHEN [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_IP%' OR [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_Ot%' THEN [CSDConstruction].[dbo].[NETWORK_EXPENSE].[Expense_AMT] as [e].[Expense_AMT] ELSE NULL END), [Expr1074]=COUNT_BIG(CASE WHEN [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_OP%' THEN [CSDConstruction].[dbo].[NETWORK_EXPENSE].[Expense_AMT] as [e].[Expense_AMT] ELSE NULL END), [Expr1075]=SUM(CASE WHEN [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_OP%' THEN [CSDConstruction].[dbo].[NETWORK_EXPENSE].[Expense_AMT] as [e].[Expense_AMT] ELSE NULL END)))
| | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[CEGroup_Type_ID]))
| | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[Network_Expense_ID]))
| | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_EXPENSE].[IX_NETWORK_EXPENSE_NETWORK_ID] AS [e]), SEEK:([e].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
| | | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_EXPENSE].[PK_NetworkSpend] AS [e]), SEEK:([e].[Network_Expense_ID]=[CSDConstruction].[dbo].[NETWORK_EXPENSE].[Network_Expense_ID] as [e].[Network_Expense_ID]) LOOKUP ORDERED FORWARD)
| | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[REF_CEGROUP].[PK_REF_CEGrp] AS [ret]), SEEK:([ret].[CEGroup_Type_ID]=[CSDConstruction].[dbo].[NETWORK_EXPENSE].[CEGroup_Type_ID] as [e].[CEGroup_Type_ID]), WHERE:([CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3%') ORDERED FORWARD)
| | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_APPROVAL].[IX_NETWORK_APPROVAL] AS [a]), SEEK:([a].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
| | | | | | | | | | | | | |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_APPROVAL].[IX_NETWORK_APPROVAL] AS [a]), SEEK:([a].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
| | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[REF_NTWK_APPROVAL_STATUS].[PK_REF_MILESTONE_VERSION] AS [ra]), SEEK:([ra].[Approval_Type_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_Type_ID] as [a].[Approval_Type_ID]), WHERE:([CSDConstruction].[dbo].[REF_NTWK_APPROVAL_STATUS].[Approval_NM] as [ra].[Approval_NM]='LNA') ORDERED FORWARD)
| | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK].[PK_DimNetwork] AS [n]), SEEK:([n].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
| | | | | | | | | | |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK].[PK_DimNetwork] AS [n]), SEEK:([n].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
| | | | | | | | | |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_APPROVAL].[IX_NETWORK_APPROVAL] AS [b]), SEEK:([b].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID] AND [b].[Approval_Type_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_Type_ID] as [a].[Approval_Type_ID] AND [b].[Approval_Version_NUM]=[Expr1034]) ORDERED FORWARD)
| | | | | | | | |--Index Seek(OBJECT:([CSDConstruction].[dbo].[APPROVAL_VINTAGE].[IX_APPROVAL_VINTAGE] AS [av]), SEEK:([av].[Approval_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_ID] as [b].[Approval_ID]) ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_APPROVAL].[IX_NETWORK_APPROVAL] AS [b]), SEEK:([b].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID] AND [b].[Approval_Type_ID]=[CSDConstruction].[dbo].[REF_NTWK_APPROVAL_STATUS].[Approval_Type_ID] as [ra].[Approval_Type_ID] AND [b].[Approval_Version_NUM]=[Expr1008]) ORDERED FORWARD)
| | | | | | |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_MILESTONE].[IX_NETWORK_MILESTONE] AS [m]), SEEK:([m].[Approval_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_ID] as [b].[Approval_ID]) ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_MILESTONE].[PK_MILESTONE] AS [m]), SEEK:([m].[Milestone_ID]=[CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_ID] as [m].[Milestone_ID]) LOOKUP ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_MILESTONE].[IX_NETWORK_MILESTONE] AS [m1]), SEEK:([m1].[Approval_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_ID] as [b].[Approval_ID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_MILESTONE].[PK_MILESTONE] AS [m1]), SEEK:([m1].[Milestone_ID]=[CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_ID] as [m1].[Milestone_ID]) LOOKUP ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [nisr]), WHERE:([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [nisr].[Milestone_NM]='NISR'))
| |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [unknown]), WHERE:([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [unknown].[Milestone_NM]='UNKNOWN'))
|--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [fcom]), WHERE:([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [fcom].[Milestone_NM]='FCOM'))
Here is what the data looks like:
Network_ID Network_NUM Network_NM Network_ID Milestone_Type_ID Approval_ID Network_ID Approval_Type_ID Approval_Version_NUM Approval_Vintage_ID Approval_ID Milestone_Type_ID Milestone_ID Approval_ID Milestone_Type_ID Milestone_DT Milestone_Type_ID Milestone_NM Status_Type_ID Network_Status_ID Network_ID Status_Type_ID Status_DT Current_IND
613693 2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB 613693 6 716823 613693 1 2 85707 716823 6 2493490 716823 6 20080229 6 NISR 6 352472 613693 1 20010307 0
613693 2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB 613693 6 716823 613693 1 2 85707 716823 6 2493490 716823 6 20080229 6 NISR 6 633324 613693 2 20010614 0
613693 2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB 613693 6 716823 613693 1 2 85707 716823 6 2493490 716823 6 20080229 6 NISR 6 948846 613693 3 20030924 0
613693 2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB 613693 6 716823 613693 1 2 85707 716823 6 2493490 716823 6 20080229 6 NISR 6 1264368 613693 4 20031007 0
613693 2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB 613693 6 716823 613693 1 2 85707 716823 6 2493490 716823 6 20080229 6 NISR 6 1579890 613693 5 20090515 0
613693 2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB 613693 6 716823 613693 1 2 85707 716823 6 2493490 716823 6 20080229 6 NISR 6 1895412 613693 6 20090515 0
613693 2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB 613693 6 716823 613693 1 2 85707 716823 6 2493490 716823 6 20080229 6 NISR 6 2210934 613693 7 20100526 0
613693 2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB 613693 6 716823 613693 1 2 85707 716823 6 2493490 716823 6 20080229 6 NISR 6 2526456 613693 8 20100901 0
618637 2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH 618637 6 720878 618637 1 2 92712 720878 6 2497545 720878 6 20090327 6 NISR 6 457111 618637 1 20031020 0
618637 2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH 618637 6 720878 618637 1 2 92712 720878 6 2497545 720878 6 20090327 6 NISR 6 637149 618637 2 20031027 0
618637 2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH 618637 6 720878 618637 1 2 92712 720878 6 2497545 720878 6 20090327 6 NISR 6 952671 618637 3 20081210 0
618637 2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH 618637 6 720878 618637 1 2 92712 720878 6 2497545 720878 6 20090327 6 NISR 6 1268193 618637 4 20090811 0
618637 2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH 618637 6 720878 618637 1 2 92712 720878 6 2497545 720878 6 20090327 6 NISR 6 1583715 618637 5 20090911 0
618637 2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH 618637 6 720878 618637 1 2 92712 720878 6 2497545 720878 6 20090327 6 NISR 6 1899237 618637 6 20090911 0
618637 2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH 618637 6 720878 618637 1 2 92712 720878 6 2497545 720878 6 20090327 6 NISR 6 2214759 618637 7 20100305 0
618637 2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH 618637 6 720878 618637 1 2 92712 720878 6 2497545 720878 6 20090327 6 NISR 6 2530281 618637 8 20100701 0
621017 2135192 RAD:NWTN:8 LOT SUBD EDINBURGH DR N/O 88 621017 6 555884 621017 1 3 71742 555884 6 2332551 555884 6 20091127 6 NISR 6 542606 621017 1 20040525 0
621017 2135192 RAD:NWTN:8 LOT SUBD EDINBURGH DR N/O 88 621017 6 555884 621017 1 3 71742 555884 6 2332551 555884 6 20091127 6 NISR 6 639371 621017 2 20040525 0
621017 2135192 RAD:NWTN:8 LOT SUBD EDINBURGH DR N/O 88 621017 6 555884 621017 1 3 71742 555884 6 2332551 555884 6 20091127 6 NISR 6 954893 621017 3 20091020 0
621017 2135192 RAD:NWTN:8 LOT SUBD EDINBURGH DR N/O 88 621017 6 555884 621017 1 3 71742 555884 6 2332551 555884 6 20091127 6 NISR 6 1270415 621017 4 20091020 0
Trying to select the subset where the 3rd and 6th columns from the end match.
