CODEWITHSUNDEEP
sql-serversql-server-2008-r2ssmsisnullsqlperformance
THX-1138
THX-1138

Reputation: 21730

Using ISNULL to handle optional parameter hurts performance

SQL Server 2008 R2

Note, I understand that performance issues can get complicated. My question is more general and pertains to behavior of ISNULL construct in general.

I have a SQL query that needs to optionally filter by given parameter, I accomplished that by appending following clause to the query:

WHERE tbl.Col1 = ISNULL(@col1, tbl.Col1)

What I see is that my query executes fine from within SSMS, but when fed to dapper it times out.

If I replace filter with following, behaviorally equivalent expression:

WHERE @col1 IS NULL OR tbl.Col1 = @col1

the query executes without timeout.

Question: why? Is there a known issue with ISNULL when used in this scenario that slows down query execution?

(I have not tried profiling the query yet, but will do that next)

Upvotes: 0

Views: 958

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Note: this question it's about ISNULL(@col1, tbl.Col1) and not about ISNULL(tbl.Col1, @col1)

WHERE tbl.Col1 = ISNULL(@col1, tbl.Col1)

1) You may add RECOMPILE query hint. In this case, SQL Server will [re]compile this query at every execution. This means that will reevaluate following predicate tbl.Col1 = ISNULL(@col1, tbl.Col1) at every execution and

  • if @col contains NULL then it will remove the condition is the column is mandatory (NOT NULL; because WHERE tbl.Col1 = ISNULL(@col1, tbl.Col1) => WHERE tbl.Col1 = tbl.Col1), else

  • if @col contains something (is not null) then WHERE tbl.Col1 = ISNULL(@col1, tbl.Col1) is translated to WHERE tbl.Col1 = @col1

Example (Adventure Works for SQL Server 2012):

enter image description here

Note: if @col1 and tbl.Col1 have different data types then SQL Server will convert tbl.Col1 values to the data type of @col1. This implicit conversion could block a Seek on tbl.Col1 (when @col1 is not null).

2) WHERE @col1 IS NULL OR tbl.Col1 = @col1 are SARG-able predicates and this means that SQL Server can filter rows using a Seek (if there is a index on tbl.Col1).

See also Conor vs. more SARGable predicates.

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

Functions in the WHERE clause prevent SQL from using indexes on the field. So if Col1 has a nonclustered index on Col1, it can be used in your second example, but not the first. This will cause the function to evaluate for each row (a clustered index scan), a potentially major performance hit.

See articles for details and examples:

Database Journal

MSSQLTips

Upvotes: 1

Related Questions