select records based on list

dodorisio · December 12th, 2003, 02:14 PM

right now i have a query that is:

SELECT bla, bla, bla FROM table WHERE bla='foo' OR bla = 'foo' OR...

ect.. but the problem is i have 40 possible values that bla can be.

is there a way to simplify this query so that will will be quicker and less expensive by using a list?

daniel

Jeff Mason · December 12th, 2003, 02:18 PM

Use the IN clause:

Code:

SELECT bla, bla, bla FROM table WHERE bla IN ('foo1','foo2','foo3',...)

Though if you have 40 of them, you might be better storing them in a table and JOINing instead.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

planoie · December 12th, 2003, 04:21 PM

Well you could do something like this...

WHERE bla IN ('foo','foo',...)

I don't know if this is any better. My understanding is that the IN operation is more expensive that explicit tests.

Peter
------------------------------------------------------
Work smarter, not harder.

Jeff Mason · December 12th, 2003, 04:39 PM

Quote:

quote:
My understanding is that the IN operation is more expensive than explicit tests.

Actually, I think you'll find that the query plan is the same for both IN and explicit OR tests. The IN compiles to multiple OR's, so there's no difference between them execution-wise. I think the IN test is easier to read and understand, especially if there are several values being tested, even if it isn't any faster.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

sal · December 12th, 2003, 11:59 PM

Jeff, I thought that IN was a faster way than OR. I have seen that in many books whan discussing query optimization. I have never actually tested it.

Sal

Jeff Mason · December 13th, 2003, 07:23 AM

Quote:

quote:
I thought that IN was a faster way than OR. I have seen that in many books when discussing query optimization.

As I said, I think you'll find that the query plan is identical in either case.

Try it.

Using the Northwind database, I first executed the following query in QA with the "show execution plan" option on:

Code:

SELECT customerid, companyname FROM customers
WHERE country = 'brazil' OR country = 'spain' OR country = 'usa'

if you hover the cursor over the first (rightmost) operation (the clustered index scan), you'll see each predicate in the selection listed in an 'or' condition.

Next, try the following equivalent query:

Code:

SELECT customerid, companyname FROM customers
WHERE country IN ('brazil','spain','usa')

Look at that plan and you will see that the two are absolutely identical.

Since the plans are the same, I assume their execution time would be the same, though I haven't measured them.

I'm not sure what books you've read that say that IN is faster than OR, but I don't think that's true...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

alyeng2000 · December 13th, 2003, 01:29 PM

i think the idea of choosing between IN and OR as That IN is better performance than OR, i think it depends on the Dbengine which the query run at , i am not sure if SqlServer Query optimizer equalize the performance of OR To As executing IN , but i think based on what sal read that IN is best the reason is that for OR query the search engine have to rescan the table much times depending on how much statment which Or separate, but in query have IN would only scan the table only one time and chech the field if it have either values of the IN array list values

Ahmed Ali
Software Developer

Jeff Mason · December 13th, 2003, 01:46 PM

Quote:

quote:
... i think it depends on the Dbengine which the query run at ... but i think based on what sal read that IN is best the reason is that for OR query ...the search engine have to rescan the table much times depending on how much statment which Or separate, but in query have IN would only scan the table only one time and chech the field if it have either values of the IN array list values.

Well, since this is a SQL Server forum, I assume we're limiting our discussion to SQL Server.

As far as possible multiple table scans, that may be what you think it might be doing, but the reality is that it doesn't work that way.

I don't know how many times I have to say it, but IN with a list of values and OR tests result in the same query plan. They will perform the same. If you don't believe me, try a test, inspect the query plan, and see for yourself.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com