Performance of a SQL Server Scalar Function

Question

CREATE FUNCTION GETBUSINESSDATEDIFF
(
    @startdate as DATETIME,
    @enddate as DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @res int

SET @res = (DATEDIFF(dd, @startdate, @enddate) + 0)
    -(DATEDIFF(wk, @startdate, @enddate) * 2)
    +(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
    -(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)

    RETURN @res
END
GO

I have this SQL Server scalar function used in one of my stored procedures (I have about 1 million rows of data). With this function it takes about 40 seconds to execute.

AND dbo.GETBUSINESSDATEDIFF(L.Date4, L.Date2) <= 4

But if I move the logic directly to my stored procedure instead of calling the function it returns data within 1 second.

AND ((DATEDIFF(dd, L.Date4, @ToUTCDate) + 0)
    -(DATEDIFF(wk, L.Date4, L.Date2) * 2)
    +(CASE WHEN DATEPART(dw, L.Date4) = 1 THEN 1 ELSE 0 END)
    -(CASE WHEN DATEPART(dw, L.Date2) = 7 THEN 1 ELSE 0 END)) <= 4

Any suggestions on how to improve this?

Answer 1

Scalar functions are a performance nightmare because they basically run RBAR (Row-By-Agonizing-Row).

You need to re-write using a different method e.g.

• Write directly in your procedure [ideal]
• Using a table-valued function