471,089 Members | 1,116 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

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

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


Feb 8 '07 #1
2 1311
serge (se****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 8 '07 #2
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" <se****@nospam.ehmail.comwrote in message
news:oA*********************@wagner.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

Feb 8 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by David B. Held | last post: by
9 posts views Thread by christopher diggins | last post: by
92 posts views Thread by Jim Langston | last post: by
33 posts views Thread by Ney André de Mello Zunino | last post: by
5 posts views Thread by Kevin | last post: by
3 posts views Thread by aj | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.