8 reputation
4
bio website
location Vancouver
age
visits member for 2 years
seen Sep 2 '12 at 21:39

I'm a virgo.


Aug
27
comment SQL Server Query: Inefficient where clause
Sorry if you were confused. I had said I only need the Network_ID, and the difference between the Status_DT and the Milestone_DT. My requirements were consistent, but perhaps my communication of them needs to be better. Anyways, thanks for your help.
Aug
25
awarded  Student
Aug
23
awarded  Commentator
Aug
23
comment SQL Server Query: Inefficient where clause
If you want to discuss it further, we can chat about it.
Aug
23
comment SQL Server Query: Inefficient where clause
well, it is doing more than making the text longer, since it is impacting the query execution plan! I need the fields from NETWORK_STATUS, so I have to have it in my select. Not much value in a fast query if the fields aren't there. I was hoping you would understand how this trick worked, but I guess it's a lucky hack. I honestly expected to have to resort to using compiler hints to prevent the unnecessary scans of the Network_Status table.
Aug
23
comment SQL Server Query: Inefficient where clause
The semantics are identical, as it should be since I need the fields. Our thought is that the filter (in the original query) in the where clause was being applied too early in the execution plan, causing O(n) scanning of that larger table (2m rows). By using the Where Exists clause, it must be forcing the filter to be applied at the very end of the execution plan after the result set has been made much smaller. 8 minutes -> 3 seconds. Not bad!
Aug
23
comment SQL Server Query: Inefficient where clause
if you're lost, then so am I. All I know is your suggestion works to speed it up: select * from dbo.NETWORK AS n [ ... rest of query ommitted... ] inner join dbo.REF_MILESTONE AS rm on km.Milestone_Type_ID = rm.Milestone_Type_ID inner join dbo.NETWORK_STATUS as s on n.Network_ID = s.Network_ID and rm.Status_Type_ID = s.Status_Type_ID WHERE EXISTS ( SELECT 1 FROM dbo.NETWORK_STATUS WHERE Network_ID = n.Network_ID and Status_Type_ID = rm.Status_Type_ID );
Aug
23
comment SQL Server Query: Inefficient where clause
thanks for these tips. I was thinking the same thing when I posted.
Aug
23
awarded  Scholar
Aug
23
accepted SQL Server Query: Inefficient where clause
Aug
23
comment SQL Server Query: Inefficient where clause
Also, there is some possible optimization wrpt the views. vwNetwork_KeyMstn and vwApproval_Latest reuse some of the tables used in this query. However, the rowset I am using here is not large. NETWORK_STATUS is the biggest at 2m rows, however the rest of my dataset is approx. 50k rows.
Aug
23
comment SQL Server Query: Inefficient where clause
I will not be using select * in production... I only need the Network_ID, and the difference between the Status_DT and the Milestone_DT. Select * here is only for debugging. Also, I had to add the NETWORK_STATUS table back since you had moved it into the subquery, and it still works as desired.
Aug
23
comment SQL Server Query: Inefficient where clause
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.
Aug
23
comment SQL Server Query: Inefficient where clause
my bad, I didn't read it carefully enough on how you intended it. This now runs in 3s. Colour me humbled.
Aug
23
comment SQL Server Query: Inefficient where clause
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.
Aug
23
awarded  Editor
Aug
23
comment SQL Server Query: Inefficient where clause
Status_Type_ID = 2 is actually a testing line. I need the line in there that is currently commented out.
Aug
23
revised SQL Server Query: Inefficient where clause
Added execution plan and sample data
Aug
23
asked SQL Server Query: Inefficient where clause