How can I improve the performance of SQL Server Scalar function?

Question

I have the following function:

 

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 used this SQL Server scalar function in one of my stored procedures which has about 1 million rows of data. It takes around 40 seconds to execute.

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

 

The moment I shift the logic directly to my stored procedure rather than 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

How can I improve this?

Answer 1

Actually it is very tedious proves to achieve performance from Scalar function because they actually run RBAR which is Row By Agonizing Row. 

You have to write it again using a different method such as:

• Write directly in your procedure 

• Using a table-valued function

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.