October 20, 2009 at 1:32 pm
I have the update statement below utilizing a NOT IN. Is running rather slow, but I don't think there is another way around it. The patid and mardbname combo are necessary
update pharm.dbo.cms_medorderimport set import_rec_status = 'A' where
import_rec_status = 'i' and
(patientid + mardbname) not in
(select patid + mardbname from pat..cmspatviewl)
October 20, 2009 at 1:37 pm
Try this:
update orderimport
set import_rec_status = 'A'
from pharm.dbo.cms_medorderimport as orderimport
left outer join pat..cmspatviewl vw
on orderimport.patientid = vw.patid
and orderimport.mardbname = vw.mardbname
where orderimport.import_rec_status = 'i'
and vw.patid is null;
Might be faster.
Also, guessing from the name, pat..cmspatviewl wouldn't happen to be a view would it? If so, it might make sense to replace the view with a more efficient CTE in this query. In many cases, views cause significant performance hits, because they often involve more tables and columns than are really needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2009 at 1:43 pm
I have the view limited to only necessary columns
October 20, 2009 at 1:47 pm
Excellent. Then the join should speed things up considerably. Assuming you have indexes it can use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2009 at 2:50 pm
I wonder, how this would work?
update pharm.dbo.cms_medorderimport
set import_rec_status = 'A'
from pharm.dbo.cms_medorderimport imp
where
import_rec_status = 'i'
and not exists (select 1 from pat..cmspatviewl
where imp.patientid = patid
and imp.mardbname = mardbname)
Regards
Piotr
...and your only reply is slàinte mhath
October 20, 2009 at 2:57 pm
I guess the whole question which is more efficient, not in, not exists, left join
October 20, 2009 at 3:54 pm
timscronin (10/20/2009)
I guess the whole question which is more efficient, not in, not exists, left join
AFAIK, there's no straight answer to that question. Each of them written correctly can win in certain situations. I've seen tests before that put left join or IN slightly behind the others. Curiously, I've never seen tests that put EXISTS as slower than the other two, but that doesn't mean they don't exist. They should all be approximately equal performance wise, but it never hurts to test them in your given situations.
October 21, 2009 at 6:46 am
The main reason that the Not In version is slow is almost certainly that it's concatenating columns in the Where clause. That kind of thing almost always results in scans and/or poor join math. Turning it into a join on the two separate columns allows for better use of indexes (assuming they exist) and better use of stats for the join math.
The Not Exists correlated sub-query will almost certainly end up with the same execution plan as the Left Outer Join. At least, that's what I've seen most times.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 21, 2009 at 9:21 am
GSquared (10/21/2009)
The Not Exists correlated sub-query will almost certainly end up with the same execution plan as the Left Outer Join. At least, that's what I've seen most times.
That's the same I've noticed. NOT EXISTS is my favourite, I think it makes the whole thing more readable.
-- Gianluca Sartori
October 21, 2009 at 2:47 pm
Gianluca Sartori (10/21/2009)
GSquared (10/21/2009)
The Not Exists correlated sub-query will almost certainly end up with the same execution plan as the Left Outer Join. At least, that's what I've seen most times.That's the same I've noticed. NOT EXISTS is my favourite, I think it makes the whole thing more readable.
Definitely a valid reason to pick one over the other when they're the same performance.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply