Performance Question

Hello Everybody.
Not sure how sql server query optimizer works ?
if i have a query like following.
select * from table where field1 = 'A' or field2 = 'B' or field2 = 'C'
so how the above query will execute ? if field1 = 'A' is true, the other
part of the query field2 = 'B' or field3 = 'C' will execute or not ?
because i have a very complecated query with the same concept.
Pls let me knowIn this case if any OR expression is true it will stop processing the list
as that satisfies the WHERE clause. But you can not guarantee which order
the expressions are evaluated. It is up to the engine to determine that at
run time and as such any of the columns can be addressed first.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F3BC8CDD-E914-45FD-8A74-74E3BBD922C0@.microsoft.com...
> Hello Everybody.
> Not sure how sql server query optimizer works ?
> if i have a query like following.
> select * from table where field1 = 'A' or field2 = 'B' or field2 = 'C'
> so how the above query will execute ? if field1 = 'A' is true, the other
> part of the query field2 = 'B' or field3 = 'C' will execute or not ?
> because i have a very complecated query with the same concept.
> Pls let me know
>|||On Fri, 16 Dec 2005 11:53:02 -0800, mvp wrote:

>Hello Everybody.
>Not sure how sql server query optimizer works ?
>if i have a query like following.
>select * from table where field1 = 'A' or field2 = 'B' or field2 = 'C'
>so how the above query will execute ? if field1 = 'A' is true, the other
>part of the query field2 = 'B' or field3 = 'C' will execute or not ?
>because i have a very complecated query with the same concept.
>Pls let me know
Hi mvp,
What Andrew says is correct, *IF* the optimizer decides to use a table
scan or an index scan to satisfy the query. However, there are also
completely different techniques that the optimizer might choose.
For instance, if both field1 and field2 are indexed, and both indexes
are sufficiently selective, the optimizer might decide to do individual
index lookups for each of the three comparisons, then combine the
results to get the end result. To see an example of this, run the
following query against Northwind and check the execution plan:
SELECT *
FROM Orders
WHERE ShippedDate = '19980506'
OR OrderID = 10550
OR OrderID = 11067
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql