A Simple Refactoring – Functions in the WHERE Clause
Putting functions in the where clause of a SQL Statement can cause performance problems. If you aren’t careful, these problems can add up and bring a powerful production system to its knees.
For this example, I’m using the Northwind database, but you could do this on any database.
The first thing to do is put an index on the OrderDate column:
CREATE INDEX IX_Orders_OrderDate ON dbo.Orders (OrderDate) ;
Take a look at this first query:
SELECT COUNT(*) FROM dbo.Orders AS o WHERE DATEADD(MM, 2, o.OrderDate) < '1997-05-04' ;
This returns very quickly on the Northwind database because we have such a small volume of data, but a query like this could cause a lot of problems in production. Why? Well, it’s doing a full table scan. Don’t believe me? Take a look a the execution plan over there.
The database is forcing a table scan because of the use of the DATEADD function on an indexed column. SQL Server will have to compute the value of every OrderDate plus 2 months in the database and then compare it to May 4th, 1997.
What happens if we flip this around? What if we change our query so that we’re doing the math on our input value of May 4th, 1997?
SELECT COUNT(*) FROM dbo.Orders AS o WHERE o.OrderDate < DATEADD(MM, -2, '1997-05-04') ;
It turns out that if we move the function from the table to the variable (although it’s really inlined in this case) we can get SQL Server to perform an index seek. This is going to be orders of magnitude faster than scanning every row in the table and comparing the values.
So, there you have it: another easy fix that can save you a lot of headache when you’re trying to tune queries.
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.