Avoid Using Functions in WHERE Clause




By:
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.

Explain Plan - Function On Column

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.

Explain Plan - Function Not On Column

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.

CPU Reads Writes Duration
Function 5 274 0 43
No Function 0 5 0 5
Additional Information

Last Update: 2/17/2014




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools