April 4, 2002 at 12:51 pm
I am trying to fine tune a query and I'm not sure which operator (IN or OR) is faster? I'm worried that the IN operator will negate the use of an index.
Thanks,
R.
April 4, 2002 at 12:59 pm
they both will work well. The IN resolves to essentially a series of ORs
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2002 at 2:24 pm
Look at the operators precedence if you are using Oracle, the "IN" comes 3 levels before "OR", in SQL Server they are on the same level.
April 4, 2002 at 7:37 pm
Also IN is easier to read. And depending on the needs of the query and the number of items in your IN it may build an internal constants table that it can use to speed up the query. You can also look at the execution plan to see what effect OR or IN has.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 7:46 pm
I agree that IN is easier to read. I typically use OR if there are only two choices, IN for any more than that.
Andy
April 5, 2002 at 7:51 am
Thanks everyone for responses ...
I found this at http://www.sql-server-performance.com/transact_sql.asp
"Try to avoid WHERE clauses that are non-sargable. Non-sargable search arguments in the WHERE clause, such as "IS NULL", "OR", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" can often (but not always) prevent the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, or expressions that have the same column on both sides of the operator, are not sargable.
But not every WHERE clause that has a non-sargable expression in it is doomed to a table scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use an index (if one exists) to help access the data quickly. [6.5, 7.0, 2000]
*****
If possible, use EXISTS and IN instead of NOT EXISTS or NOT IN, as they are more efficient and will boost your query's performance. The use of NOT EXISTS and NOT IN can prevent the use of indexes. [6.5, 7.0, 2000]
*****
When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is more efficient and performs faster. [6.5, 7.0, 2000] Added 8-7-2000
*****
If your SELECT statement includes an IN option along with a list of values to be tested in the query, order the list of values so that the most frequently found values are placed at the first of the list, and the less frequently found values are placed at the end of the list. This can speed performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes. [6.5, 7.0, 2000]"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply