Why a simple T-SQL UDF function makes the code execution 3 times slower

Question

I'm rewriting some old stored procedure and I've come across an unexpected performance issue when using a function instead of inline code.

The function is very simple as follow:

ALTER FUNCTION [dbo].[GetDateDifferenceInDays] 
(       
@first_date SMALLDATETIME, 
@second_date SMALLDATETIME
)
RETURNS INT 
AS
BEGIN 

RETURN ABS(DATEDIFF(DAY, @first_date, @second_date))

END

So I've got two identical queries, but one uses the function and the other does the calculation in the query itself:

ABS(DATEDIFF(DAY, [mytable].first_date, [mytable].second_date))

Now the query with the inline code runs 3 times faster than the one using the function.

Answer 1

What you have is a scalar UDF ( takes 0 to n parameters and returns a scalar value ). Such UDFs typically cause a row-by-row operation of your query, unless called with constant parameters, with exactly the kind of performance degradation that you're experiencing with your query.

See here, here and here for detailed explanations of the peformance pitfalls of using UDFs.

Answer 2

Depending on the usage context, the query optimizer may be able to analyze the inline code and figure out a great index-using query plan, while it doesn't "inline the function" for similarly detailed analysis and so ends up with an inferior query plan when the function is involved. Look at the two query plans, side by side, and you should be able to confirm (or disprove) this hypothesis pretty easily!