Speeding up queries

Question

I have this function that adds working days to a specific date and returns the date:

ALTER FUNCTION [dbo].[CalcWorkDaysAddDays]
    (@StartDate AS DATETIME, @Days AS INT) 
RETURNS DATE 
AS 
BEGIN 
    DECLARE @Count INT = 0 
    DECLARE @WorkDay INT = 0 

    DECLARE @Date DATE = @StartDate 

    WHILE @WorkDay < @Days 
    BEGIN 
        SET @Count = @Count + 1 

        SET @Date = DATEADD(DAY, @Count, @StartDate) 

        IF NOT (DATEPART(WEEKDAY, @Date) IN (1, 7) 
                OR EXISTS (SELECT * 
                           FROM Holidays_Weeknds 
                           WHERE Date = @Date)) 
        BEGIN 
            SET @WorkDay = @WorkDay + 1 
        END 
    END 

    RETURN @Date 
END

This function works perfectly in a small dataset. However I have almost 70k records in my table and I need to use this function for more than 10 columns in my table. The problem is that when I use this function it takes too long to run. Is there any way I can speed it up or maybe modify the UPDATE statement that updates the columns in my table? My update statement looks like this:

UPDATE pt
SET [Predicted_Eng_Comp_date] = [dbo].[CalcWorkDaysAddDays](pt.Actual_Eng_Start_date, 20)
FROM [cntrra20-devbrs].PST.[dbo].[tbl_Project_tracker1] pt

Rather than having a `Holiday_Weeknds` table, have you considered having a calendar table (one that store one row for *every* day and then indicates whether it's a working day/weekend/holiday, etc and any other useful data to you) instead? It should allow you to write the query using a set-based join rather than trying to put it in a function. — Damien_The_Unbeliever, Apr 26 '17 at 08:09
What records exactly are contained in your `Holidays_Weeknds` table? If records for all saturdays, sundays and holidays, then it will be easy too (but not that universal than using calendar table). — Arvo, Apr 26 '17 at 08:09
The holiday_weekends is a view that has filtered out all the working days and left the holidays and weekends. This comes from a calendar — Otshepeng Ditshego, Apr 26 '17 at 08:41

score 2 · Answer 1 · edited May 23 '17 at 11:54

Some general hints:

Scalar functions are bad performers
WHILE loops (any loop actually) are bad performers
procedural thinking is the wrong approach

If you need a function, the best is an inline table valued function, even if it returns just one scalar value.

Your issue

This is a perfect situation for a persitant date's table.

You find one example here
Aaron Bertrand offers a great approach here.
Use your Holiday-table to add an IsHoliday-Flag to the table.

This is a tiny mock-up to show the principles

DECLARE @mockup TABLE(TheDate DATE, DaysName VARCHAR(100),IsWeekday BIT, IsHoliday BIT)
INSERT INTO @mockup VALUES
 ({d'2016-12-19'},'Mo',1,0)
,({d'2016-12-20'},'Tu',1,0)
,({d'2016-12-21'},'We',1,0)
,({d'2016-12-22'},'Th',1,0)
,({d'2016-12-23'},'Fr',1,0)
,({d'2016-12-24'},'Sa',0,0)
,({d'2016-12-25'},'Su',0,1)
,({d'2016-12-26'},'Mo',1,1)
,({d'2016-12-27'},'Tu',1,0)
,({d'2016-12-28'},'We',1,0)
,({d'2016-12-29'},'Th',1,0)
,({d'2016-12-30'},'Fr',1,0)
,({d'2016-12-31'},'Sa',0,0)
,({d'2017-01-01'},'Su',0,1)
,({d'2017-01-02'},'Mo',1,0)
,({d'2017-01-03'},'Tu',1,0)
,({d'2017-01-04'},'We',1,0);

--Your variables

DECLARE @d DATE={d'2016-12-20'};
DECLARE @WorkdaysToAdd INT=5;

--The query picks the TOP X sorted ASC, and picks the TOP 1 sorted DESC

SELECT TOP 1 TheDate
FROM
(
    SELECT TOP (@WorkdaysToAdd) TheDate 
    FROM @mockup 
    WHERE TheDate>@d
      AND IsWeekday=1
      AND IsHoliday=0 
    ORDER BY TheDate ASC
) AS t
ORDER BY t.TheDate DESC

UPDATE

You might create a function from this or include it withing paranthesis directly into your UPDATE query (instead of your function).

Hi all. I managed to get it right. the problem was that this function refers to a table thats in another server. My fault there. It runs in less than 5 seconds — Otshepeng Ditshego, May 03 '17 at 14:05

cloudsafe · Accepted Answer · 2017-04-26T11:48:02.320

Columns with functions are very slow. Try to avoid:

   declare @MinStartDate as date = '1/1/2010'
   declare @MaxEndDate as date = '1/1/2020'
   declare @numberofdays int = 20

   ;WITH res(val)
    AS
    ( 
    select val from (select @MinStartDate as val) as resy
    union all
    select dateadd(d,1, res.val) from res where res.val < @MaxEndDate
    )
    UPDATE pt
    set [Predicted_Eng_Comp_date]= a.NUMDAYS
    from [cntrra20-devbrs].PST.[dbo].[tbl_Project_tracker1] pt
        inner join (
            SELECT  r.val, count(*) as NUMDAYS
            FROM [cntrra20-devbrs].PST.[dbo].[tbl_Project_tracker1] pt
                inner join res r on pt.Actual_Eng_Start_date = r.val
                left join Holidays_Weeknds h on r.val = h.[Date]
            where DATEPART(WEEKDAY, r.val) NOT IN (1,7) and h.[Date] is null 
                 and val>=pt.Actual_Eng_Start_date and val<=dateadd(d,@numberofdays, pt.Actual_Eng_Start_date)
             group by r.val) a on pt.Actual_Eng_Start_date = a.val
    option (MAXRECURSION 32767)

Depending on calculation, you may want to change 'val<=dateadd' to val less than.

This one returns the error: An aggregate may not appear in the set list of an UPDATE statement — Otshepeng Ditshego, Apr 26 '17 at 11:24

Speeding up queries

2 Answers2

Some general hints:

Your issue

UPDATE