SQL Server Performance Forum – Threads Archive

OR or IN

Hi, I have a query that is dynamically built by my application and that may include a lot of OR-clauses (in fact it could be several hundreds) so I am wondering if it would be faster for me to have the arguments in an IN-clause instead. Current solution:
SELECT * FROM categories where categoryid=1 OR categoryid=2 OR categoryid=3 (and so on) Would it be faster to change it to:
SELECT * FROM categories where categoryid IN (1, 2, 3)? Regards,
Mille

---

The IN keyword allows you to select rows that match any one of a list of values.
You can check the query execution plan between these 2 queries. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Another posibility that might save you the use of dynamic query is to create a function that returns a rowset from the string and join it to your table. Check this link for an excellent way of doing it provided by Joe Chang.
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1057
You are limited here to 8000 characters though. Bambola.

---

Thanks for your replies. Regards,
Mille

---

Don’t use OR if you can help it. SQL Server is not the best at optimising queries to use the best selection of indexes with OR statements in them. We have had to mess about with deleting column stats and splitting queries into UNIONs to get around the problem.

---

]]>