Slow "not in" sql

Last Post 06 Aug 2008 06:14 AM by SwePeso. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
timcronin
New Member
New Member

--
05 Aug 2008 07:02 AM
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

SwePeso
New Member
New Member

--
06 Aug 2008 06:14 AM
NOT EXISTS will be faster than NOT IN.
Or LEFT JOIN with IS NULL
You are not authorized to post a reply.

Acceptable Use Policy