Use of Functions Part - II

Hi,

Let's continue with usage of functions in Where clause.

As we know that SQL Server offers many functions that can be used either in SELECT clause or in WHERE clause. We can develop our own user defined functions. When functions are used in the SELECT clause to return uppercase output, it doesn't affect performance that much, but when functions are used improperly in the WHERE clause these functions can cause major performance issues.

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 we are only returning a handful of rows. 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.

Following are some simple statements that shows 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
This 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
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
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.

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.

Suggestions:

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

HAPPY SQL CODING.