In vs =, performance considerations

Question

In vs =, performance considerations

robolance

SSC-Addicted

Points: 403
More actions
November 13, 2014 at 8:12 am

#292384

I am tuning a very long and slow running process.
Somewhere in the past I heard/saw a discussion about how indexes are handled when using an 'IN' vs. using '='.
For example:
SELECT This, That, Other FROM sometable
WHERE this in (1,2,3)
vs
SELECT This, That, Other FROM sometable
WHERE this = 1 OR this = 2 OR this = 3
Is there any difference? I am doing testing and finding very little if any. What oother considerations should I keep in mind?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

There's no difference. SQL Server will interpret them as the same query.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

robolance SSC-Addicted Points: 403 More actions · Answer 2

That's the way I am leaning. After reading a bit, it appears that SQL Server engine will try to optimize the order of the items in the 'IN' clause. This would be a specific use case that would make a large difference rarely.

Other than that, it all comes down to readability.

The query in question has a list of about 50 terms. My long term plan is to take these items into a table and use a join to get the same effect. They do change occasionally. Short term, I am trying to break this into modular parts and I want to really understand what is going on with each piece of the query.

So I will move on to the next piece of the query and see if I can find anything with more impact there.

I appreciate you input.

g.britton SSChampion Points: 13863 More actions · Answer 3

robolance (11/13/2014)
I am tuning a very long and slow running process.
Somewhere in the past I heard/saw a discussion about how indexes are handled when using an 'IN' vs. using '='.
For example:
SELECT This, That, Other FROM sometable
WHERE this in (1,2,3)
vs
SELECT This, That, Other FROM sometable
WHERE this = 1 OR this = 2 OR this = 3
Is there any difference? I am doing testing and finding very little if any. What oother considerations should I keep in mind?

Is column 'this' indexed? If not, SQL will need a table scan to find them. If the table is large....

Gerald Britton, Pluralsight courses

robolance SSC-Addicted Points: 403 More actions · Answer 4

Yes. Looking at the execution plan, it is using the index for both options.

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

If you look at the execution plan, you'll see that SQL converts the IN list into multiple OR conditions, so there's no difference.