Avoid Using Functions in WHERE Clause

By: Ben Snaidero

Overview

As was mentioned in an earlier topic function calls do make code more readable but in some cases they cause other unwanted effects that reduce the performance of our queries. Certain scenarios of using a function in a WHERE clause is one of those cases.

Explanation

There are two reasons why you want to avoid having a function call in your WHERE clause and more specifically on the columns you are filtering on in your WHERE clause. The first is the same as the previous topic, the function needs to be called for every record in the result set which can slow down your query performance. The second reason which can have even more impact on query performance is the fact that if there is a function surrounding the column you are trying to filter on, any indexes on that column can not be used. Let's take a look at a simple example. First we'll need to create an index on the column in our WHERE clause so we can show how it's not used when we add a function around it. Here is the code.

CREATE NONCLUSTERED INDEX idxParent_DateDataColumn
ON [dbo].[Parent] ([DateDataColumn]) 

-- cleanup statements
DROP INDEX Parent.idxParent_DateDataColumn

Now let's look at a simple query which would return all the records in the Parent table that are less than 30 days old. Here is one way that we could write the SQL statement.

SELECT ParentID
  FROM [dbo].[Parent]
 WHERE dateadd(d,30,DateDataColumn) > getdate()

Looking at the explain plan for this query we can see that the index on the DateDataColumn that we created is ignored and an index scan is performed.

Now let's rewrite this query and move the function to the other side of the > operator. Here is the SQL statement.

SELECT ParentID
  FROM [dbo].[Parent]
 WHERE DateDataColumn > dateadd(d,-30,getdate())

Looking at the explain plan for this query we can see that the optimizer is now using the index and performs a seek rather than a scan.

To confirm that it is indeed faster let's take a look at the SQL Profiler results for these two queries. We can see below that when using an index, as is usually the case, we use fewer resources and our statement executes faster.

Additional Information

• More examples on functions in WHERE clause causing issues with performance