sql question using IN
-
And I always thought EXCEPT is better than NOT IN or NOT EXISTS or LEFT OUTER JOIN 
(Profiler is your best friend) everyday is a new day at this forum. 
Heh... I think Remi said it best a while back... with 400k to 500k members, you're bound to see something different somewhere along the line.
Obviously, NOT IN can only be used for single column conditions but I think they're more intuitive to read than the outer join methods. Turns out, they're just as fast or faster (except on some laptops for some reason) than the outer join method, as well.
The reason why the EXISTS is a bit slower is because it's a correlated sub-query that get's executed once for each target row. Don't have 2k5 to test with but suspect EXCEPT works in a similar manner. AS you saw in my test, doesn't make a huge difference whether it's NOT IN, NOT EXISTS, or EXCEPT over a million rows, but did want to dispell the myth about NOT IN being bad.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lol Jeff. So true for the team part. I'll let you do the work and I'll make the money. How's that for teamwork
.BTW I hardly call working 18 hours/day 7 days/week being lazy
... but that's only my opinion. -
I'd be curious to see a test of this :
1 M rows table :
Select * FROM 1MTbl WHERE SomeCol NOT IN (SELECT 995000 Rows from OtherBigAsstbl)
I'd be curious to compare that to a more positive method (in, exists, join) VS exclusion... off course that is no test to compare the difference between join, in and exists.
-
I think you'll find that WHERE IN has the exact same execution plan and performance as good and proper joins. I don't like them just because the code comes out a lot different than what I'm used to reading...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
After all the opinions I think I will learn to use the inner join, but the IN statement is easier for me to debug and test...
Jim
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply