August 5, 2008 at 7:44 am
I have the below tsql running on a new sql box with 32 gig of ram
select a.FacID, a.PatID, PatName, RoNo
from Pat..Reorders a
inner join Pat..Patients b on a.FacID = b.FacID and a.PatID = b.PatID
inner join Pat..vPatNames c on a.facid = c.facid and a.patid = c.patid
where len(NDC) > 0 and NDC not in (select NDC from Drug..KeyIdentifiers)
order by a.FacID, PatName, RoNo
The reorders table is 7 mill rows properly indexed the Keyidentifiers is 188333 rows with the NDC as the clustered index. the query buries the server. Looking at the tsql below, doesn't this do the same? I am at a loss why the 1st query runs so poorly (Indexes rebuilt recently on all affected tables)
select a.FacID, a.PatID, PatName, RoNo
from Pat..Reorders a
inner join Pat..Patients b on a.FacID = b.FacID and a.PatID = b.PatID
inner join Pat..vPatNames c on a.facid = c.facid and a.patid = c.patid
left outer join Drug..KeyIdentifiers d on a.ndc = d.ndc
where len(a.NDC) > 0 and
d.ndc is null
It runs in a few seconds
August 5, 2008 at 8:05 am
Your NOT IN clause would mean checking against every single value to make sure there is no match.
Check the execution plan for the query, then try
AND NOT EXISTS (SELECT * FROM drug..keyidentifiers AS K WHERE K.NDC = .NDC) - [ I can't figure out from your query which ref to use for the NDC column]
Where as the NOT EXISTS clause can find a definite match
I think it will have a much more efficient execution plan.
August 5, 2008 at 8:20 am
also something else to consider;
wouldn't the where statement where len(a.NDC) > 0 force a table scan on the Reorders table in order to calculat ethe length of the field? i think that might slow down the query as well, right?
Lowell
August 5, 2008 at 8:30 am
is Reorders.NDC indexed? if not that would explain the difference since the outer join to KeyIdentifiers would be supported by KeyIdentifier's clustered index but the IN() would have no supporting index in Reorders.
August 5, 2008 at 8:42 am
both indexed
August 5, 2008 at 4:20 pm
Tom Brown (8/5/2008)
Your NOT IN clause would mean checking against every single value to make sure there is no match.Check the execution plan for the query, then try
AND NOT EXISTS (SELECT * FROM drug..keyidentifiers AS K WHERE K.NDC = .NDC) - [ I can't figure out from your query which ref to use for the NDC column]
Where as the NOT EXISTS clause can find a definite match
I think it will have a much more efficient execution plan.
as explained NOT IN will check every single row, hey welcome to waitsville. How many millions of rows does it have again?????? NOT EXISTS would be a lot more efficient, avoid NOT IN like the plague
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 5, 2008 at 4:35 pm
Could you please attach the XML Execution Plans for both of these queries. I am very interested if trying to figure out what cases cause this to happen (despite what you may hear, it is not that common in 2005).
Also if you could include the Table def's or at least the column defs for NDC in both tables and the Clustered index definition, it would help me out a lot.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 6, 2008 at 2:06 am
Table structures and Execution plans will give a better look.
I think I would have gone for Left Outer Join as I believe that it is faster than NOT In. Instead of Not In, Not Exists will be faster (to avoid full table scan...)
Atif Sheikh
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply