2
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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CH81
  • 387
  • 4
  • 12
  • 1
    Scalar functions are a performance nightmare because they basically run RBAR (Row-By-Agonizing-Row https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/). You need to re-write using a different method e.g. write directly in your procedure [ideal] or using a table-valued function. – gvee Mar 23 '15 at 17:40

1 Answers1

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
gvee
  • 16,732
  • 35
  • 50
  • 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. :) – CH81 Mar 23 '15 at 21:41
  • Not anymore - at least in some cases [Scalar UDF inlining](https://stackoverflow.com/a/53192700/5070879) – Lukasz Szozda Dec 20 '18 at 20:11