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

Question

Hi folks,

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.

Can you please explain me why?

Thanks,

Giuseppe

Answer 1

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!

Answer 2

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 3

Don't use a slow scalar UDF, use a fast inline one. Examples here:

Reuse Your Code with Table-Valued UDFs

Calculating third Wednesday of the month with inline UDFs

Many nested inline UDFs are very fast

The question is very common: it has been asked and answered hundreds of times before, as such it has a few canned answers.