Last post Sep 23, 2018 03:45 PM by wmec
Sep 23, 2018 06:00 AM|ctee|LINK
I am having performance issue for query below where I am trying to get the repair count. The repair count will count according to Model Number, Repair Part and Repair Date. Every repair/replacement will increase the count +1. However, if
the Repair Type is Checking, no count will be counted for this. Hence, the count will follow the last repair count.
Please refer to the raw data, expected result and query. When I am searching for a single model number, I have no performance issue. But, if I want to check for the whole Car Brand, it took a few minutes to return me the result. Anyone have any better suggestion
to improve the performance of my query?
Declare @tempPart table ( RowNum int,
repairID int INDEX IX1 NONCLUSTERED,
modelNumber varchar(100) ,
insert into @tempPart (RowNum,repairID ,modelNumber ,repairDate ,loopCount,repairPart)
select ROW_NUMBER() OVER(ORDER BY lr.repairDate, lpt.repairPart) RN1,
(case when lrw.reworkID =1
or ls.statusID not in (2,10) then 0 else 1 end )
as loopCount, lpt.repairPart from RepairRequest lr
LEFT JOIN RepairPart lpt ON lr.repairID = lpt.repairID
LEFT JOIN MasterRework lrw ON lpt.reworkID = lrw.reworkID
INNER JOIN MasterStatus ls ON lr.statusID = ls.statusID
where lr.isActive=1 and lpt.isactive=1
AND lr.modelNumber ='33A004352'
group by lr.repairID,lr.modelNumber,lr.repairDate ,lrw.reworkID
SELECT r.repairID, r.modelNumber,sloop.loopCount as 'loopCount'
FROM RepairRequest r
LEFT JOIN RepairPart pt ON r.repairID = crd.repairID
left join (
select a.modelNumber,a.repairID, Sum(b.loopCount) as loopCount, a.repairDate,a.repairPart
from (select * from @tempPart ) a
left join (select * from @tempPart) b
on a.RowNum >= b.RowNum and a.modelNumber = b.modelNumber
group by a.modelNumber,a.repairID,a.repairPart ,a.repairDate
) sloop on sloop.repairID = r.repairID
and sloop.repairDate = r.repairDate
WHERE r.isActive=1 and pt.isActive=1
AND r.modelNumber ='33A004352'
order by r.repairDate asc, pt.repairPart asc
Sep 23, 2018 03:45 PM|wmec|LINK
Do you mean that you check CarBrandID? Do you have index on it? If not, you can create it.