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' ;

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?

SELECT  COUNT(*)
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.

Comments

4 Comments so far. Comments are closed.
  1. Nighthawk,

    Your 2nd option is incorrect; since the dates are inverted so should the offset. It should be “… DATEADD(MM, -2, …”

  2. Sure, but it shouldn’t matter in this particular case. Check my blogpost at http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx and the associated Connect item.

    But also, consider the fact that if you were doing a join between two tables, you might find that your query needed to have a function on a column in one of the tables. Moving it to the right doesn’t help, because you’re still telling the system that it can’t consider the original form of one of the columns. I go into this in another recent post.
    http://msmvps.com/blogs/robfarley/archive/2010/02/02/a-case-study-in-sargability.aspx

    And in fact, will be covering this in a LiveMeeting session in April: http://www.sqlpass.org/Events/ctl/ViewEvent/mid/521.aspx?ID=334

    • Hey Rob – thanks for taking the time to share some of your blog posts and especially your LiveMeeting for PASS in April!

      I’m aware that this is a contrived example. I was using this as a recipe to show a basic technique for developers who are getting started with SQL and looking to apply a simple refactoring to their T-SQL to make it run well.

Trackbacks

One Trackback Trackbacks are closed.

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 531 items have been purified.