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?

Scalar functions are a performance nightmare because they basically run RBAR (Row-By-Agonizing-Row simple-talk.com/sql/t-sql-programming/…). You need to re-write using a different method e.g. write directly in your procedure [ideal] or using a table-valued function. — gvee
– gvee, Commented Mar 23, 2015 at 17:40

gvee · Accepted Answer · 2015-03-23 17:42:54Z

3

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

answered Mar 23, 2015 at 17:42

gvee

17.2k42 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CH81 Over a year ago

Thanks for the answer. I converted the Scalar-valued Function to a Table-valued Function and did a CROSS APPLY. That decreases the time to run the query by half. But still not as same as directly applying the logic in to the WHERE clause. We are talking about 40 seconds, 20 seconds and 1 second. :)

Lukasz Szozda Over a year ago

Not anymore - at least in some cases Scalar UDF inlining

Collectives™ on Stack Overflow

Performance of a SQL Server Scalar Function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related