CODEWITHSUNDEEP
performancesql-server-2005user-defined-functions
Brian
Brian

Reputation: 25834

Scalar-Valued Function Slower than Expected

CREATE function [dbo].[fn_GetDateOnly](@dateWithTime datetime)
    returns datetime WITH SCHEMABINDING
as
begin
    return DATEADD(DAY, DATEDIFF(DAY, 0, @dateWithTime), 0)
end


SELECT stuff, count(ff.id)
FROM dbo.foo AS ff 
where
--DATEADD(DAY, DATEDIFF(DAY, 0, ff.startDate), 0) <= @curdate --6700ms
--dbo.fn_GetDateOnly(ff.startDate) <= @curdate --9300ms
group by stuff
order by stuff desc

Why is calling fn_getdateonly so much slower than inlining it? Doesn't SQL inline the function call?

Upvotes: 1

Views: 1195

Answers (1)

ErikE
ErikE

Reputation: 50201

Expressions on Columns

First, no matter what function you use, putting it in the WHERE clause or in a JOIN condition on a column in the table is suboptimal. Do the math on a constant and compare. Your WHERE clause should look like this:

ff.startDate < DateAdd(day, 1, @curdate) -- if @curdate has time portion removed
ff.startDate < DateAdd(day, 1, dbo.fn_GetDateOnly(@curdate)) -- if @curdate has time

For generically finding items on a given date, use this pattern:

WHERE
   DateCol >= '20120901'
   AND DateCol < '20120902'

Put any functions on the opposite side of the equal sign as the column, which should be alone. It may help you to look up how to make an expression SARGable. If a column has to be on both sides, put all the expressions on the side that is the "left" input in the execution plan (its data comes first, is the outer loop of a LOOP JOIN or the "table" side of a HASH JOIN). For example, if you're trying to do this:

WHERE dbo.fn_getDateOnly(A.DateCol) = dbo.fn_getDateOnly(B.DateCol)

then assuming A.DateCol comes first in the execution plan, switch it to:

WHERE
   B.DateCol >= DateAdd(day, DateDiff(day, 0, A.DateCol), 0)
   AND B.DateCol < DateAdd(day, DateDiff(day, 0, A.DateCol), 0)

(Or use the inline version of the function below, but I find it just as awkward so no extra value to being indirect).

If this kind of querying is going to be done frequently on the tables involved, then some redesign is probably in order, either to add a persisted computed column having the time portion removed (that is possibly indexed), to split the datetime into separate date and time fields, or to store it as simply a date to begin with (if you really don't need datetime).

Note: of course, the references to dbo.fn_getDateOnly can be simply replaced with DateAdd(day, DateDiff(day, 0, DateCol), 0). Additionally, if the value has the datetime data type, you can just do DateCol + 1 rather than using DateAdd (though be careful, since this won't work on the date data type in SQL 2008 and up).

The Inline-ability of UDFs

As for the function's performance in the SELECT clause, SQL Server only inlines "inline functions", not scalar functions. Change your function to return a single-row recordset CREATE FUNCTION ... RETURNS TABLE AS RETURN (SELECT ...) and use it like so:

SELECT
   (SELECT DateValue FROM dbo.fn_GetDateOnly(Col)),
   ...

It really is no different than a parameterized view.

Using this inline version in the WHERE clause is going to be clumsy. It is almost simply better to do the DateDiff. In SQL 2008, of course just use Convert(date, DateCol) but still follow the rules about where to put calculation expressions (on the opposite side of the equal sign from the column).

Be sure to vote for inline scalar UDFs on Microsoft Connect! You're far from the only one who thinks this functionality is sorely lacking.

Upvotes: 3

Related Questions