1

I am trying to optimize a query which has many parameters via Profiler. I tested below queries:

declare @location nvarchar(200) = null;
select count(*) from Places where @location is null or Location = @location 
cpu:1249 duration:64 reads:456760
declare @location nvarchar(200) = null;
select count(*) from Places
cpu:315 duration:20 reads:15001

As you can see, both queries are identical because the @location parameter is optional and it is null, but performance metrics are showing very different results. It seems SQL Server couldn't understand both queries are exactly the same.

Why SQL Server can not optimize such a simple query?

3

both queries are identical because the @location parameter is optional and it is null

They are on this invocation, but the execution plan for the query will be cached and may be reused when the @location parameter has a different value. SQL Server has to build a plan that will produce correct results for all possible cases.

To resolve this, you could write the query using Dynamic SQL, or you could use an option (recompile) query hint. More details, including the trade-offs for each method, in the references below:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.