solving sql server problems for millions of dbas and developers since 2006

Avoid SQL Server functions in the WHERE clause for Performance

By: Greg Robidoux | Read Comments (2) | Print

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

Related Tips: More

Problem
SQL Server offers many handy functions that can be used either in your SELECT clause or in your WHERE clause. For the most part these functions provide complex coding that would be very difficult to get this same functionality without these functions. In addition to the built in functions you also have the ability to develop your own user defined functions. When functions are used in the SELECT clause to return uppercase output, a substring or whatever, it doesn't affect performance that much, but when functions are used improperly in the WHERE clause these functions can cause major performance issues.

Solution
When functions are used in the SELECT clause, the function has to be run with each data value to return the proper results. This may not be a bad thing if you are only returning a handful of rows of data. But when these same functions are used in the WHERE clause this forces SQL Server to do a table scan or index scan to get the correct results instead of doing an index seek if there is an index that can be used. The reason for this is is that the function value has to be evaluated for each row of data to determine it matches your criteria.

Here are some simple statements that show you the affect of using a function in the WHERE clause. To get a better understanding of how these queries are working we are also getting the query plan. This can be done by hitting Ctrl-M in a query window to turn this function on before running the query.

Example 1
This first example uses the LEFT function to get the first two characters of the email address. Once this is done each row is evaluated to see if it matches the "As" criteria. The EmailAddress is indexed, so SQL Server should be able to use an index seek to find the data.

SELECT EmailAddress
FROM person.contact
WHERE left(EmailAddress,2) = 'As'

From the query plan output we can see that this query does an index scan, which means it reviews all rows before returning the results. This is because of the LEFT function that is being used.

Another version of this same query which will return the same results uses the LIKE clause instead. This query uses the like clause to get all data that begins with "As". Since there is an index on the the EmailAddress column SQL Server can do an index seek which is much more efficient then an index scan.

SELECT EmailAddress
FROM person.contact
WHERE EmailAddress like 'As%'

Example 2
Here is another example where the UPPER clause is used to transform the EmailAddress into upper case before evaluating the data. Again the EmailAddress is indexed.

SELECT EmailAddress
FROM person.contact
WHERE upper(EmailAddress) like 'AS%'

We can see that the query plan for this also does an index scan versus an index seek.

A second version of this query again just uses the LIKE clause to get the same results.

SELECT EmailAddress
FROM person.contact
WHERE EmailAddress like 'AS%'

Again this query does an index seek versus a index scan.

Example 3
Here is another example where the function DateDiff is used. The function is getting rows where the difference in minutes between the ModifiedDate and the getdate() function is greater then zero. In addition, column ModifiedDate is indexed.

SELECT ModifiedDate
FROM person.contact
WHERE datediff(minute,ModifiedDate,getdate())>0

This first query is using the function and therefore an index scan has to occur.

In this version we are just doing a straight comparison of ModifiedDate compared to getdate().

SELECT ModifiedDate
FROM person.contact
WHERE ModifiedDate < getdate()

Since we are not using a function this query is using an index seek.

Summary
The data that was used to run these queries was the data in the AdventureWorks database. This database is quite small compared to most SQL Server installations, so the query time results as well as the overall I/O overhead that may be imposed by doing index scans versus index seeks will be quite different and also probably show some significant improvements in your environment. Not using functions in the WHERE clause is a simple thing to avoid and can provide big performance gains if use alternative methods.

Next Steps

Look for poor performing statements in your databases where scans are occurring to see if functions are being used in the WHERE clause
Look for alternative methods for getting the same query results, such as some of the examples have shown

Related Tips: More | Become a paid author

Last Update: 5/3/2007

Share: