Remove Function Calls From the SELECT List

By: Ben Snaidero

Overview

Using functions as a part of any type of programming is usually a good practice as it generally makes code more readable and allows you to use to use it over and over again. This is also true for SQL queries except for the fact that there are cases when running the same statement over and over again might not be the most efficient way to get your result.

Explanation

Let's take a look at a high level what happens when you use a function in the SELECT list of a query. Basically that function needs to be called for every record returned by the query. If this function contains a multi table join to do some sort of lookup this process could get quite expensive. In some cases in makes sense to remove this function call and simply join the tables from the function directly to the other tables in your query. To illustrate this point let's take a simple query that does a lookup into another table. Below is some SQL we will need to run before out test in order to create our function and add an index on the lookup column.

CREATE FUNCTION fn_getParentDate (@ParentID bigint) RETURNS datetime AS
BEGIN
 DECLARE @DateData datetime
 SELECT @DateData = DateDataColumn from [dbo].[Parent] where ParentID=@ParentID
 RETURN @DateData
END
GO

CREATE NONCLUSTERED INDEX idxChild_ParentID
ON [dbo].[Child] ([ParentID]) 

-- cleanup statements
--DROP INDEX Child.idxChild_ParentID
--DROP FUNCTION fn_getParentDate

Now we can write a simple query that calls this function and which does a lookup in the parent table. Here is the statement.

SELECT dbo.fn_getParentDate(ParentID),ChildID 
  FROM [dbo].[Child]

Looking at the explain plan for this query we can see it's going to do a scan of the Child table which makes sense since there is no WHERE clause and for each row returned it uses the index on the Parent table to do a seek for the lookup.

Now let's rewrite this query and instead of using the function call let's just join the Parent table in our query and add the DateDataColumn to our SELECT list. Here is the statement.

SELECT P.DateDataColumn,ChildID 
  FROM [dbo].[Parent] P INNER JOIN
       [dbo].[Child] C ON P.ParentID=C.ParentID

Looking at the explain plan for this query we can see it only has to access the Parent table once but it now has to do a scan of this table before it performs a merge join.

It's not entirely clear from just looking at the above explain plans which statement will perform better. The index seek in the query with the function might lead you to believe that it would be faster but let's run the statements and take a look at the SQL Profiler results below. We can see from these results that in fact the query without the function ran more than twice as fast and used considerably less resources than the one that uses the function call.

Additional Information

• Removing function calls for better performance