Why optimizer is not smart? Is dynamic SQL my only option...

[serge]

declare @ContactId as integer

set @ContactId = 5

select *

from Person.Contact

where ContactId = @ContactId

OR @ContactId = -1



If you run this in SQL 2005 on the AdventureWorks database,

why the logical reads is 561

Table 'Contact'. Scan count 1, logical reads 56



and not 2 when you run without the second OR condition:

declare @ContactId as integer

set @ContactId = 5

select *

from Person.Contact

where ContactId = @ContactId



How can i use the same SP and either get one record returned

by passing the ID of the field, or pass a dummy parameter like

-1 in order to get ALL the records returned.

In this case even when i pass a parameter like ContactID = 5

there is still a table scan (clustered index scan in this case)

happening for the other OR condition.

There's no method to tell SQL to start checking the first condition

whether or not it is true then if it is false then check the second OR

conditon. On the same topic does this mean all OR conditions are

ALWAYS verified regardless if one of them has already been determined

to be True?



Thank you

[Erland Sommarskog]

Re: Why optimizer is not smart? Is dynamic SQL my only option...

serge (sergea@nospam. ehmail.com) writes:

declare @ContactId as integer
set @ContactId = 5
select *
from Person.Contact
where ContactId = @ContactId
OR @ContactId = -1
>
If you run this in SQL 2005 on the AdventureWorks database,
why the logical reads is 561
Table 'Contact'. Scan count 1, logical reads 56
>
and not 2 when you run without the second OR condition:

Because the optimizer computes the plan, it does not know what the value of
@ContactId will be when the statement is executed.

I have a whole article on my web site on this topic,
http://www.sommarskog.se/dyn-search.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Dan Guzman]

Re: Why optimizer is not smart? Is dynamic SQL my only option...

To add on to Erland's response, OPTION (RECOMPILE) is a quick way to get a
better plan but it's not optimal in this case because there are 2 mutually
exclusive results in the same query. IMHO, the best approach in this case
is to formulate the query differently using an IF or UNION ALL.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@nospam. ehmail.comwrote in message
news:oAyyh.9495 5$vT5.1877048@w agner.videotron .net...

declare @ContactId as integer
>
set @ContactId = 5
>
select *
>
from Person.Contact
>
where ContactId = @ContactId
>
OR @ContactId = -1
>
>
>
If you run this in SQL 2005 on the AdventureWorks database,
>
why the logical reads is 561
>
Table 'Contact'. Scan count 1, logical reads 56
>
>
>
and not 2 when you run without the second OR condition:
>
declare @ContactId as integer
>
set @ContactId = 5
>
select *
>
from Person.Contact
>
where ContactId = @ContactId
>
>
>
How can i use the same SP and either get one record returned
>
by passing the ID of the field, or pass a dummy parameter like
>
-1 in order to get ALL the records returned.
>
In this case even when i pass a parameter like ContactID = 5
>
there is still a table scan (clustered index scan in this case)
>
happening for the other OR condition.
>
There's no method to tell SQL to start checking the first condition
>
whether or not it is true then if it is false then check the second OR
>
conditon. On the same topic does this mean all OR conditions are
>
ALWAYS verified regardless if one of them has already been determined
>
to be True?
>
>
>
Thank you
>
>
>
>