Remove Function Calls From the SELECT List




By:
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 [email protected]
 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.

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

Last Update: 2/17/2014




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools