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:

FROM    dbo.Orders AS o
WHERE   DATEADD(MM, 2, o.OrderDate) < '1997-05-04' ;

Scanning the stars… or something.

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?

FROM    dbo.Orders AS o
WHERE   o.OrderDate < DATEADD(MM, -2, '1997-05-04') ;

Seek and ye shall find.

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.