Remove Function Calls From the SELECT List


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.


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
 DECLARE @DateData datetime
 SELECT @DateData = DateDataColumn from [dbo].[Parent] where ParentID=@ParentID
 RETURN @DateData

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.

Explain Plan - Function

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.

Explain Plan - No Function

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.

CPU Reads Writes Duration
Function 14985 5705126 0 25982
No Function 578 5933 0 11964
Additional Information

Comments For This Article

get free sql tips
agree to terms