Subscribe to this blog
Briefcase
Print
This continues my series on Common SQL Server mistakes, looking at more T-SQL mistakes.
What’s Wrong?
If you saw a query like this, would you see a problem?
o.OrderID
, o.CustomerID
, o.Qty
from Orders o
where datepart( yyyy, o.OrderDate) = '2010'
If there are 1,000 orders in this table, there probably isn’t an issue. But if there are 1,000,000, then this is an issue.
Why? Let’s examine the execution plan:
This table has 1000 rows in it, but it doesn’t use indexing to find those orders that were placed in 2010. Instead it scans all rows. The reason is that the function being used in the WHERE clause means that the index cannot be used.
Instead, what you would want to do is write the query like this:
o.OrderID
, o.*
, o.Qty
from [OrderItems] o
where o.OrderDate >= '20100101'
In this way, we eliminate the function from the WHERE clause and allow the query optimizer to take advantage of the indexes on the column OrderDate.
You see similar issues with queries like:
lastname
from Person.Contact
where left(Lastname, 1) = 'S'
This can be fixed as:
lastname
from Person.Contact
where Lastname like 'S%'
Basically you want to move the function away from the column and put it on the other side of the comparison so that indexes can be used.
Too often we have developers writing queries like this, assuming that the functions are efficient. They are, but when they are executed against every row in a table, an index can’t be used for seek operations, which are always quicker than scans for any significant data set.
When you are writing queries, do your best to avoid functions against columns in your tables. Instead try to rework the query to move the function. An alternative that I’ll blog about another time is computed columns.
Posted by Dukagjin Maloku on 27 October 2010
Nice and simple warning! Nice explanation, Steve, thnx for sharing.
Posted by jcrawf02 on 27 October 2010
check this out, Jeremiah found an interesting way around this if you needed to use it
facility9.com/.../a-simple-refactoring-functions-in-the-where-clause
Posted by Steve Jones on 27 October 2010
That's a nice technique Jeremiah has. Personally I'd recalculate things and have a set variable in the WHERE clause, but either work.