Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you show an actual execution plan, as well as describe the indexes on the tables? Some sample data and desired results would be good as well, and describing which of these represent unique joins (e.g. can a Network_ID in NETWORK_STATUS have multiple rows with the same or different status_type_id values)? –  Aaron Bertrand Aug 23 '12 at 20:00
    
From the plan, it looks like I'm scanning the NETWORK_STATUS index for every row that is returned in order to perform the filter in the where clause. Unless I am reading this wrong. –  Gregory Johnson Aug 23 '12 at 20:18
    
No idea. Your showplan_text output is not exactly the plan I was hoping to see, and you have to admit that's a ton of content to expect anyone trying to help you for free to parse through. Could you please post a .sqlplan file somewhere (and perhaps one that includes the alternative I posted as well, for comparison)? dba.blogoverflow.com/2012/06/help-us-help-you –  Aaron Bertrand Aug 23 '12 at 20:24
    
thanks for these tips. I was thinking the same thing when I posted. –  Gregory Johnson Aug 23 '12 at 20:51
add comment

1 Answer 1

up vote 4 down vote accepted

If you don't need the other data from the NETWORK_STATUS table, how about:

select *
from dbo.NETWORK AS n
inner join dbo.vwNETWORK_KEYMSTN AS km 
  on n.Network_ID = km.Network_ID
inner join dbo.vwAPPROVAL_LATEST AS a 
  on n.Network_ID = a.Network_ID
inner join dbo.APPROVAL_VINTAGE  AS av 
  on a.Approval_ID = av.Approval_ID 
  and km.Milestone_Type_ID = av.Milestone_Type_ID
inner join dbo.NETWORK_MILESTONE AS m 
  on A.Approval_ID = m.Approval_ID 
  and km.Milestone_Type_ID = m.Milestone_Type_ID
inner join dbo.REF_MILESTONE AS rm 
  on km.Milestone_Type_ID = rm.Milestone_Type_ID
WHERE EXISTS
(
  SELECT 1 FROM dbo.NETWORK_STATUS 
    WHERE Network_ID = n.Network_ID
    and Status_Type_ID = 2
    --and Status_Type_ID = rm.Status_Type_ID
);
share|improve this answer
    
Status_Type_ID = 2 is actually a testing line. I need the line in there that is currently commented out. –  Gregory Johnson Aug 23 '12 at 20:15
    
Ok, so do you need me to comment it out in my answer? Or can you test it either way? –  Aaron Bertrand Aug 23 '12 at 20:15
    
my bad, I didn't read it carefully enough on how you intended it. This now runs in 3s. Colour me humbled. –  Gregory Johnson Aug 23 '12 at 20:24
    
Fast isn't the only criteria here. Does it produce the correct results? Do you have all the columns you need? Have you considered ditching the SELECT * and actually listing only the columns you need? Have you looked to see if any of those joins are redundant? –  Aaron Bertrand Aug 23 '12 at 20:26
    
is it possible for you to explain a bit your thought process on how you came up with this structure? I'm guessing this subquery causes the O(n) scan of the NETWORK_STATUS index to reduce to O(1), but I do not understand why. –  Gregory Johnson Aug 23 '12 at 20:37
show 11 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.