CODEWITHSUNDEEP
sql-servert-sqlssrs-2008
DavidStein
DavidStein

Reputation: 3179

IN vs = in Where Clauses

I try to create standardized SQL scripts for use in my SSRS reports. If I wish to have a statement such as: 

Select * from mytable

and use a SQL Variable (SSRS parameter) in the where clause, is there any speed advantage to using this:

Where field = @MyField

VS.

Where field IN (@MyField)

I know the second option supports both multiple selections and single selections while the first supports only single.

Are there any performance hits if I write all my queries with the IN statement for uniformity?

Upvotes: 1

Views: 762

Answers (3)

user55474
user55474

Reputation: 537

the performance does get slow if you have a large no of item in your IN clause

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

There is no performance hit. The optimizer converts the IN to a series of = operations ORed together.

As a side note, I hope you're not really using SELECT * in your actual queries.

Upvotes: 3

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30875

For one argument optimizer will change that IN into =, in real execution statement.

As both of this arguments are Sargeble (Joe Stefanelli thanks for attention); the difference in performance may by found where you have sequence value in in clause, for example IN (1,2,3,4,5,6) is slower that between 1 and 6, because for the IN the index has to be read for each value in this case 6 time for between only once.

Upvotes: 0

Related Questions