Slow running query

Transact-SQL
1

I am running this in SQL Server 2019. I am looking for a better way to write this query. It runs very slow (several minutes), if I remove the total hrs sum it is instant. I have a table of labor transactions with employee hours worked on a job and each of it's assembly operations. I have a subquery which partitions by jobnum and opseq and assigns a row number from newest to oldest entry. I then summarize each job and it's assembly operations by the last person to work on it and the total hours worked on that operation (total of every workers hours).

Query

select SQ1.name,
       SQ1.jobnum,
       sq1.opseq,
       SQ1.opcode,
       (
           select sum(laborhrs)
           from labordtl
           where jobnum = sq1.jobnum
                 and opcode = sq1.opcode
       ) as totalhrs,
       SQ1.clockindate
from
(
    SELECT [name],
           [jobnum],
           [opseq],
           [opcode],
           [clockindate],
           [clockintime],
           [clockouttime],
           [clockoutminute],
           [laborhrs],
           (ROW_NUMBER() over (PARTITION BY jobnum, opseq ORDER BY clockoutminute DESC)) as Rank
    FROM [VAWReportsTest].[dbo].[LaborDtl]
) as SQ1
where sq1.Rank = 1
order by clockindate

Sample Table Query

CREATE TABLE LaborDtl
(
    [id] int NOT NULL,
    [name] varchar(35) NOT NULL,
    [jobnum] varchar(14) NOT NULL,
    [opseq] int NOT NULL,
    [opcode] varchar(8) NOT NULL,
    [clockindate] date NULL,
    [clockintime] decimal(6, 2) NOT NULL,
    [clockouttime] decimal(6, 2) NOT NULL,
    [clockoutminute] int NOT NULL,
    [laborhrs] decimal(11, 5) NOT NULL
        PRIMARY KEY ([id])
);
INSERT INTO labordtl
(
    [id],
    [name],
    [jobnum],
    [opseq],
    [opcode],
    [clockindate],
    [clockintime],
    [clockouttime],
    [clockoutminute],
    [laborhrs]
)
VALUES
(1, 'Dave', '156200-4-1', 10, 'Eng-Mod', '2023-09-21', 9.50, 9.52, 36760891, 0.02000),
(2, 'Dave', '156200-4-1', 20, 'Eng-Dwg', '2023-09-21', 9.55, 9.57, 36760894, 0.02000),
(3, 'Dave', '156200-7-1', 10, 'Eng-Des', '2023-09-20', 7.57, 7.57, 36759334, 0.00000),
(4, 'Dave', '156200-7-1', 20, 'Eng-Mod', '2023-10-05', 12.80, 14.75, 36781365, 1.95000),
(5, 'Dave', '156200-7-1', 20, 'Eng-Mod', '2023-09-20', 7.45, 7.55, 36759333, 0.10000),
(6, 'Dave', '156200-7-1', 30, 'Eng-Dwg', '2023-09-29', 9.88, 11.92, 36772555, 2.03000),
(7, 'Dave', '156200-7-1', 30, 'Eng-Dwg', '2023-09-20', 7.57, 7.57, 36759334, 0.00000),
(8, 'Dave', '156200-8-1', 10, 'Eng-Des', '2023-08-23', 13.42, 13.42, 36719365, 0.00000),
(9, 'Dave', '156200-8-1', 20, 'Eng-Mod', '2023-08-23', 13.40, 13.42, 36719365, 0.02000),
(10, 'Dave', '156200-8-1', 30, 'Eng-Dwg', '2023-08-23', 13.42, 13.42, 36719365, 0.00000),
(11, 'Bob', '170424-1-1', 5, 'Eng-Dwg', '2019-12-06', 8.40, 8.42, 34766425, 0.02000),
(12, 'Mike', '170424-2-1', 5, 'Eng-Dwg', '2019-11-20', 14.95, 15.55, 34743813, 0.60000),
(13, 'Mike', '170424-2-1', 5, 'Eng-Dwg', '2019-11-20', 8.02, 12.25, 34743615, 4.23000),
(14, 'Richard', '170424-3-1', 5, 'Eng-Dwg', '2019-11-25', 8.40, 8.72, 34750603, 0.32000);

Results

name	jobnum	opseq	opcode	totalhrs	clockindate
Mike	170424-2-1	5	Eng-Dwg	4.83000	2019-11-20
Richard	170424-3-1	5	Eng-Dwg	0.32000	2019-11-25
Bob	170424-1-1	5	Eng-Dwg	0.02000	2019-12-06
Dave	156200-8-1	10	Eng-Des	0.00000	2023-08-23
Dave	156200-8-1	20	Eng-Mod	0.02000	2023-08-23
Dave	156200-8-1	30	Eng-Dwg	0.00000	2023-08-23
Dave	156200-7-1	10	Eng-Des	0.00000	2023-09-20
Dave	156200-4-1	10	Eng-Mod	0.02000	2023-09-21
Dave	156200-4-1	20	Eng-Dwg	0.02000	2023-09-21
Dave	156200-7-1	30	Eng-Dwg	2.03000	2023-09-29
Dave	156200-7-1	20	Eng-Mod	2.05000	2023-10-05
2

For max speed, create another index on ( jobnum, opcode ) include ( laborhrs );


select SQ1.name,
       SQ1.jobnum,
       sq1.opseq,
       SQ1.opcode,
       SQ2.totalhrs,
       SQ1.clockindate
from
(
    SELECT [name],
           [jobnum],
           [opseq],
           [opcode],
           [clockindate],
           [clockintime],
           [clockouttime],
           [clockoutminute],
           [laborhrs],
           (ROW_NUMBER() over (PARTITION BY jobnum, opseq ORDER BY clockoutminute DESC)) as Rank
    FROM [dbo].[LaborDtl]
) as SQ1
inner join
(
    SELECT jobnum, opcode, SUM(laborhrs) AS totalhrs
    FROM dbo.labordtl
    GROUP BY jobnum, opcode
) as SQ2 on SQ2.jobnum = SQ1.jobnum AND SQ2.opcode = SQ1.opcode
where sq1.Rank = 1
order by clockindate
3

Thanks, that did make a difference.

4

I may be missing something here - but why not use SUM(laborhrs) OVER(PARTITION BY jobnum, opseq)?

select SQ1.name,
       SQ1.jobnum,
       sq1.opseq,
       SQ1.opcode,
       SQ1.totalhrs,
       SQ1.clockindate
from
(
    SELECT [name],
           [jobnum],
           [opseq],
           [opcode],
           [clockindate],
           [clockintime],
           [clockouttime],
           [clockoutminute],
           [totalhrs] = SUM(laborhrs) OVER(PARTITION BY jobnum, opseq),
           (ROW_NUMBER() over (PARTITION BY jobnum, opseq ORDER BY clockoutminute DESC)) as Rank
    FROM [dbo].[LaborDtl]
) as SQ1
where sq1.Rank = 1
order by clockindate
5

Thanks, this worked and it corrected an error in the total hours that had gone unnoticed.