CODEWITHSUNDEEP
t-sql
Shabby
Shabby

Reputation: 33

Query taking more than one minute to execute

Executing my current query takes 1:16 minutes. That is way too long. Here is how my query looks like atm:

SELECT

    [plugin.tickets].[Ticket].Id,

    [plugin.tickets].[Ticket].Title,

    [plugin.tickets].[Ticket].Created,

    [plugin.tickets].[Ticket].StartProgress,

    [dbo].[worktime]([plugin.tickets].[Ticket].Created, GetDate()) AS OpenstaandeTijdInUren,

    [plugin.tickets].[Ticket].firstresponse,

    DATEDIFF(HOUR, [plugin.tickets].[Ticket].Created, [plugin.tickets].[Ticket].FirstResponse) AS ReactietijdInUren,

    [plugin.tickets].[Ticket].Status,

    [plugin.tickets].[Ticket].Priority,

         DATEDIFF (MINUTE, [plugin.tickets].[Ticket].Created, [plugin.tickets].[Ticket].EndProgress) as OplostijdTicketInUren



FROM

    [plugin.tickets].[Ticket]

WHERE

    [dbo].[worktime](

        [plugin.tickets].[Ticket].Created, GetDate()

    ) >= 1

    AND (

        [plugin.tickets].[Ticket].status <= 2

    )

ORDER BY

    [plugin.tickets].[Ticket].id

In the query there is a function I use which I made. The function calculates the difference in hours between two dates. For calculating the time excluding holidays I have made a table which contains the holidays for oncoming 10 years. Most likely thats why it is taking so long for the query to execute, because for every row it checks of the date contains same date as the holiday table. But I am not sure what the solution for this problem is?

Here is my function:

ALTER FUNCTION [dbo].[WorkTime]

(

    @StartDate DATETIME,

    @FinishDate DATETIME

)

RETURNS BIGINT

AS

BEGIN

    DECLARE @Temp BIGINT

    SET @Temp=0



       --DECLARE @hdays Table (values) date

       --insert into @hdays

       --select [Datum_Feestdag]  --= (Datum_feestdag)

       --from [dbo].[Feestdagen] 



    DECLARE @FirstDay DATE

    SET @FirstDay = CONVERT(DATE, @StartDate, 112)



    DECLARE @LastDay DATE

    SET @LastDay = CONVERT(DATE, @FinishDate, 112)



    DECLARE @StartTime TIME

    SET @StartTime = CONVERT(TIME, @StartDate)



    DECLARE @FinishTime TIME

    SET @FinishTime = CONVERT(TIME, @FinishDate)



    DECLARE @WorkStart TIME

    SET @WorkStart = '08:00'



    DECLARE @WorkFinish TIME

    SET @WorkFinish = '18:00'



    DECLARE @DailyWorkTime BIGINT

    SET @DailyWorkTime = DATEDIFF(HOUR, @WorkStart, @WorkFinish)



    IF (@StartTime<@WorkStart)

    BEGIN

        SET @StartTime = @WorkStart

    END

    IF (@FinishTime>@WorkFinish)

    BEGIN

        SET @FinishTime=@WorkFinish

    END

    IF (@FinishTime<@WorkStart)

    BEGIN

        SET @FinishTime=@WorkStart

    END

    IF (@StartTime>@WorkFinish)

    BEGIN

        SET @StartTime = @WorkFinish

    END

    DECLARE @CurrentDate DATE

    SET @CurrentDate = @FirstDay

    DECLARE @LastDate DATE

    SET @LastDate = @LastDay





    WHILE(@CurrentDate<=@LastDate)

    BEGIN     

        IF

             (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)

             and @CurrentDate not in (select Datum_feestdag from Feestdagen)

        --@CurrentDate not in (@hdays)

             BEGIN

            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)

            BEGIN

                SET @Temp = @Temp + @DailyWorkTime

            END

            --IF it starts at startdate and it finishes not this date find diff between work finish and start as hours

            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)

            BEGIN

                SET @Temp = @Temp + DATEDIFF(HOUR, @StartTime, @WorkFinish)

            END



            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)

            BEGIN

                SET @Temp = @Temp + DATEDIFF(HOUR, @WorkStart, @FinishTime)

            END

            --IF it starts and finishes in the same date

            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)

            BEGIN

                SET @Temp = DATEDIFF(HOUR, @StartTime, @FinishTime)

            END

        END

        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)

    END



    -- Return the result of the function

    IF @Temp<0

    BEGIN

        SET @Temp=0

    END

    RETURN @Temp

END

Upvotes: 0

Views: 56

Answers (1)

iamdave
iamdave

Reputation: 12243

Your query is taking longer than it should most likely because you are calculating your values row by row in a while loop. This is fundamentally not how SQL databases are supposed to be used.

Try to solve your problem by combining and comparing sets of data, rather than iterating over each value in your table. A starting point for you to develop your own solution would be something like this:

declare @t table(id int,StartDate datetime, EndDate datetime);
insert into @t values
 (1,'2019/01/01 00:00:00','2019/01/02 15:00:00')
,(2,'2019/01/02 04:00:00','2019/01/03 15:00:00')
,(3,'2019/01/02 08:30:00','2019/01/02 15:00:00')
,(4,'2019/01/04 13:45:00','2019/01/07 15:30:00')
,(5,'2019/01/07 22:00:00','2019/02/03 15:00:00');

declare @EarliestStart datetime;
declare @LatestEnd datetime;

select @EarliestStart = min(StartDate)
      ,@LatestEnd = max(EndDate)
from @t;

declare @WorkStart time = '08:00';
declare @WorkEnd time = '18:00';


with n(n) as (select t from(values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(t))    -- Crete a tally table of numbers, by taking the ROW_NUMBER of 10 rows CROSS JOINed multiple times.  In this case 6 times for 10*10*10*10*10*10 = 1,000,000 potential rows
    ,m(m) as (select top(datediff(minute,@EarliestStart,@LatestEnd)+1) dateadd(minute,row_number() over (order by (select null))-1,@EarliestStart) from n n1,n n2,n n3,n n4,n n5,n n6)  -- and add that number as minutes to the minumum StartTime up to the maximum EndTime for a list of all potentially worked minutes
    ,w(w) as (select m from m where cast(m as time) >= @WorkStart and cast(m as time) < @WorkEnd)   -- then filter this list of minutes to just those that can be worked
select t.id
        ,t.StartDate
        ,t.EndDate
        ,count(w.w)/60. as WorkingHours
        ,count(w.w) as WorkingMinutes
from @t as t
    join w
        on t.StartDate <= w.w       -- JOIN onto the list of dates to get a row for each minute actually worked between each StartDate and EndDate, that can be COUNTed for total working time
            and t.EndDate > w.w
group by t.id
        ,t.StartDate
        ,t.EndDate
order by t.id
        ,t.StartDate
        ,t.EndDate;

Output

+----+-------------------------+-------------------------+--------------+----------------+
| id |        StartDate        |         EndDate         | WorkingHours | WorkingMinutes |
+----+-------------------------+-------------------------+--------------+----------------+
|  1 | 2019-01-01 00:00:00.000 | 2019-01-02 15:00:00.000 |    17.000000 |           1020 |
|  2 | 2019-01-02 04:00:00.000 | 2019-01-03 15:00:00.000 |    17.000000 |           1020 |
|  3 | 2019-01-02 08:30:00.000 | 2019-01-02 15:00:00.000 |     6.500000 |            390 |
|  4 | 2019-01-04 13:45:00.000 | 2019-01-07 15:30:00.000 |    31.750000 |           1905 |
|  5 | 2019-01-07 22:00:00.000 | 2019-02-03 15:00:00.000 |   267.000000 |          16020 |
+----+-------------------------+-------------------------+--------------+----------------+

Upvotes: 1

Related Questions