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?