Efficiency of IN vs. other ways to filter rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Efficiency of IN vs. other ways to filter rows

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2006-08-07 : 10:25:36

Knights,

I just wonder whether I should use IN or other better ways to filter rows based on a constraint set.
I have tables and their contents look like:

declare @YearStart varchar(50), @companyID int, @SYTermID int
set @companyID = 1370
set @SYTermID = 8
set @YearStart = dbo.fn_GetSchoolStartYearOfATerm (@companyID, @SYTermID)

--1/ @YearStart for selected year
select @YearStart as '@YearStart'

@YearStart
--------------------------------------------------
2005

(1 row(s) affected)

--2/ table 'Term' and select only termID belonging to @YearStart
declare @tt table (companyID int, termID int, description varchar(50), StartDate datetime, EndDate datetime)
insert @tt
select companyID, termID, description, StartDate,EndDate
from term where companyID=@companyID
and dbo.fn_GetSchoolStartYearOfATerm (@companyID, termID)=@YearStart

--> 2A/ check @tt
select * from @tt
companyID termID description StartDate EndDate
----------- ----------- -------------------------------------------------- ------------------------------------------------------ ------------------------------------------------------
1370 8 2005 - 2006 2005-08-26 00:00:00.000 2006-08-15 00:00:00.000
1370 9 Sem 1 05/06 2005-08-26 00:00:00.000 2006-01-14 00:00:00.000
1370 11 Q1 05-06 2005-08-26 00:00:00.000 2005-10-21 00:00:00.000
1370 12 Q2 05-06 2005-10-24 00:00:00.000 2006-01-14 00:00:00.000
1370 13 Q3 05-06 2006-01-16 00:00:00.000 2006-03-17 00:00:00.000
1370 14 Sem 2 05/06 2006-01-16 00:00:00.000 2006-08-15 00:00:00.000
1370 15 Q4 05-06 2006-03-27 00:00:00.000 2006-08-15 00:00:00.000
1370 26 Progress Q1 2005-08-26 00:00:00.000 2005-10-21 00:00:00.000

(8 row(s) affected)

--> 2B/ and termIDs belonging to @YearStart of '2005'
select companyID, termID from @tt

companyID termID
----------- -----------
1370 8
1370 9
1370 11
1370 12
1370 13
1370 14
1370 15
1370 26

(8 row(s) affected)

-- 3/ table 'Class' in general (not filetered by termIDs of @tt yet)
select * from Class where companyID=@companyID

companyID classID termID CourseID
----------- ----------- ----------- -----------
1370 14 1 25
1370 15 1 26
1370 16 1 27
1370 17 1 18
1370 18 1 19
1370 19 1 20
1370 45 1 17
1370 46 1 29
1370 272 8 60
1370 47 1 18
1370 48 5 22
1370 49 2 21
1370 50 2 38
1370 275 8 50
1370 276 9 57
1370 277 14 107
1370 278 9 54

-- 4/ table 'Class' and select only class belonging to entried of table @tt.
-- The results I want as follows using IN
select companyID, classID, TermID, CourseID from Class
where companyID=@companyID and termID=@SYTermID
and termID IN (select termID from @tt)

1370 272 8 60
1370 275 8 50
1370 276 9 57
1370 277 14 107
1370 278 9 54

My questions:
A/ I should use IN or some other BETTER ways for query? runing time? Advantages?...
B/ Any ideas about the drawbacks of IN?
C/ In which situations and scenarios, IN should be used in case other ways cannot be applied?
Thanks so much for any ideas, discussions.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-08-07 : 10:45:48

i think theoretically EXISTS is faster than IN.
I prefer it too.

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 2006-08-07 : 11:01:44

IN has some limitations that EXISTS doesn't have.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-07 : 11:34:23

I've always understood EXISTS to be potentially faster than IN because it will stop processing as soon as it finds a single occurance in the subquery that satisfies the condition.

-ec

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-07 : 12:13:44

I prefer a simple JOIN.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-07 : 12:17:48

i acctually prefer the exists over in.
because you can put as many column comparisons as you like.
of course the same goes for join but i still prefer exists.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-08-07 : 12:20:56

quote:
Originally posted by jsmith8858

I prefer a simple JOIN.

- Jeff

I know it wasn't the question but...
That wouldn't help you if you needed to do the equivalent of a NOT EXISTS though

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-07 : 14:31:34

quote:
Originally posted by coolerbob

quote:
Originally posted by jsmith8858

I prefer a simple JOIN.

- Jeff

I know it wasn't the question but...
That wouldn't help you if you needed to do the equivalent of a NOT EXISTS though

Sure it does, just use a LEFT OUTER JOIN and return rows where it comes back Null.

- Jeff

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-07 : 14:45:19

I agree with jsmith8858, I have had much more success with a left join than exists statments.

johnsql
Posting Yak Master

161 Posts

Posted - 2006-08-09 : 10:16:47

Friends,
I applied the 3 ways to do the query, in order, IN, EXISTS, INNER JOIN. They give me the same DTS results
-- 4/A table 'Class' and select only class belonging to entried of table @tt
-- IN
select companyID, classID, TermID, CourseID from Class
where companyID=@companyID
and termID IN (select termID from @tt)

-- 4B/ using Exists
-- Exists
select companyID, classID, TermID, CourseID from Class cl
where cl.companyID=@companyID
and exists (select termID from @tt where termID=cl.termID)

-- 4C/ using inner join
-- INNER JOIN
select cl.companyID, cl.classID, t.TermID, cl.CourseID from Class cl
inner join @tt t on t.termID=cl.termID
where cl.companyID=@companyID

But when I refer to the Execution Plan, I get the overheads for the 3 method, in order:
Query 9: Query Cost (relative the bath): 19.88 %
Query 10: Query Cost (relative the bath): 19.88 %
Query 10: Query Cost (relative the bath): 20.42 %

Any ideas, dicussions?
Thanks in advance.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-08-09 : 10:30:17

quote:
Originally posted by jsmith8858

quote:
Originally posted by coolerbob

quote:
Originally posted by jsmith8858

I prefer a simple JOIN.

- Jeff

I know it wasn't the question but...
That wouldn't help you if you needed to do the equivalent of a NOT EXISTS though

Sure it does, just use a LEFT OUTER JOIN and return rows where it comes back Null.

- Jeff

That told me! I'll be using that!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-09 : 11:27:49

http://groups.google.de/group/microsoft.public.sqlserver.programming/tree/browse_frm/thread/1c12caa50923d3d5/f86de13e0ed65a37?rnum=1&hl=de&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.programming%2Fbrowse_frm%2Fthread%2F1c12caa50923d3d5%2Fe96cf1972f400ad9%3Fhl%3Dde%26lr%3D%26ie%3DUTF-8%26rnum%3D48%26prev%3D%2Fgroups%3Fq%3Dsql%2Bserver%2Bdifference%2Bnot%2Bin%2Bnot%2Bexists%26start%3D40%26hl%3Dde%26lr%3D%26ie%3DUTF-8%26selm%3Dumy7hwRaBHA.1916%2540tkmsftngp05%26rnum%3D48%26#doc_f530df34d5afe639 -

Madhivanan

Failing to plan is Planning to fail

pootle_flump

1064 Posts

Posted - 2006-08-09 : 11:30:04

My experience is that EXISTS is more efficient than a JOIN if wanting to only return rows from a table on the 1 side of a relationship where related records exist in the table on the many side of a relationship but I don't actually want any data from the many table. Otherwise you have the overhead of applying DISTINCT.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-08-10 : 06:07:31

"My experience is that EXISTS is more efficient than a JOIN if wanting to only return rows from a table on the 1 side of a relationship where related records exist in the table on the many side of a relationship but I don't actually want any data from the many table. Otherwise you have the overhead of applying DISTINCT."

You don't get this problem if you stay away from the "SELECT * from alltables" style of coding...and switch to "SELECT acol1, acol2, acol3 from tablea"...which is the advised approach regarding data performance anyway.

pootle_flump

1064 Posts

Posted - 2006-08-10 : 10:21:05

quote:
Originally posted by AndrewMurphy

"My experience is that EXISTS is more efficient than a JOIN if wanting to only return rows from a table on the 1 side of a relationship where related records exist in the table on the many side of a relationship but I don't actually want any data from the many table. Otherwise you have the overhead of applying DISTINCT."

You don't get this problem if you stay away from the "SELECT * from alltables" style of coding...and switch to "SELECT acol1, acol2, acol3 from tablea"...which is the advised approach regarding data performance anyway.

That is precisely what I do. SELECT * and DISTINCT can't be used together in any event. Unless I have misunderstood your meaning (or you mine).

johnsql
Posting Yak Master

161 Posts

Posted - 2006-08-15 : 15:59:43

Friends,
I learn a lot from you guys about this issue. Thanks for your great helps, ideas, and dicussions, cited articles.
johnsql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources