Any speed difference using WHERE.. IN? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Any speed difference using WHERE.. IN?

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2006-12-07 : 18:20:36

Is this type of query structure:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)

Faster than writing it as:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" = 'value1' or "column_name = 'value2', ...)

or will it make no difference?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 19:11:36

no.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-08 : 02:49:56

IN is expansion of ORs

Set the execution plan and see

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 03:22:49

Yes !

First query is definitely faster than second, because second query throws compilation error because of additional bracket at the end.

WHERE "column_name" = 'value1' or "column_name = 'value2', ...)

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-08 : 03:30:53

You have high sense of Humour

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 08:06:40

Doesn't that mean second query is faster since it never goes to table to fetch the data?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 08:15:28

Nope!

If that is fast, I can parse 1000MB of data within no time with following statement:

Update t
Set SomeNastyCol = Replace(SomeNastyCol,'', SomeNastyCol)
From MessedUpTable t

My definition of fast is when it brings some valid result.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources