CODEWITHSUNDEEP
sql-serverfunction
Otshepeng Ditshego
Otshepeng Ditshego

Reputation: 197

Speeding up queries

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

Upvotes: 0

Views: 89

Answers (2)

cloudsafe
cloudsafe

Reputation: 2504

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.

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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).

Upvotes: 2

Related Questions