0

I am working in MSSQL and I have stored procedure where I pass multiple parameters and some of them can be null. I did a research and found that if I want to include a WHERE condition with those nullable parameters, I should do it like this

DECLARE @Firstname AS VARCHAR(50) = 'Tasos'

SELECT * 
FROM Names n 
WHERE @Firstname is null or n.Firstname LIKE @Firstname + '%'

In my case I have multiple parameters, but for simplicity, I give you an example with just one parameter.

DECLARE @Firstname AS VARCHAR(50) = 'Tasos';
SELECT *
FROM Names AS n 
WHERE @Firstname IS NULL OR n.Firstname LIKE @Firstname + '%'

The above query needs about 800k logical reads and 20 secs to run. However,

DECLARE @Firstname AS VARCHAR(50) = 'Tasos';
SELECT *
FROM Names AS n 
WHERE n.Firstname LIKE @Firstname + '%'

needs only 5 logical reads with an instant result.

Can someone explain to me why the first part of the WHERE condition has such a huge impact in the performance?

EDIT: I managed to implemented by constructing the sql query dynamically as a string and then use EXECUTE sp_executesql @SQL_string;. However, my question was mostly about why the above is happening since the check is happening in the variable and it doesn't add any checks on the actual table.

Tasos
  • 7,325
  • 18
  • 83
  • 176
  • There are many caveats when using optional parameters. The difference for this case would be that the execution plan is likely optimized for a null-valued `@Firstname`, so it expects to read all the table instead of probably using your index by `Firstname`. You can solve this by adding `OPTION (RECOMPILE)`, but that has some considerations. Please check out this thread https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure – EzLo Dec 03 '19 at 14:39
  • Is there an index on Firstname column? Results both queries are the same? – Max Zolotenko Dec 03 '19 at 14:51
  • In short, I would add `OPTION (RECOMPILE)` or use dynamic SQL here (probably the former for such a simple query). [Gail Shaw](https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) and [Aaron Bertrand](https://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example) wrote some great articles on these types of queries. – Thom A Dec 03 '19 at 14:51

0 Answers0