locked
SQL Performance Issue when try to get count RRS feed

  • Question

  • User-322746205 posted

    Hi all,
    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?
    Raw Data:

    repairID carBrandID modelNumber repairDate repairPart repairType
    1119 28 33A004352 2018-06-19 08:02:02 MA1801 Replacement
    1119 28 33A004352 2018-06-19 08:02:02 MA6000 Replacement
    1158 28 33A004352 2018-07-12 03:57:30 MA1801 Replacement
    1158 28 33A004352 2018-07-12 03:57:30 MA6000 Repair
    1813 28 33A004352 2018-07-18 07:44:07 MA2110 Repair
    1830 28 33A004352 2018-08-27 23:59:41 MA2110 Checking


    Expected Result:

    repairID carBrandID modelNumber repairDate repairPart loopCount
    1119 28 33A004352 2018-06-19 08:02:02 MA1801 1
    1119 28 33A004352 2018-06-19 08:02:02 MA6000 2
    1158 28 33A004352 2018-07-12 03:57:30 MA1801 3
    1158 28 33A004352 2018-07-12 03:57:30 MA6000 4
    1813 28 33A004352 2018-07-18 07:44:07 MA2110 5
    1830 28 33A004352 2018-08-27 23:59:41 MA2110 5


    Declare @tempPart table ( RowNum int,
    repairID int INDEX IX1 NONCLUSTERED,
    modelNumber varchar(100) ,
    repairDate DateTime,
    loopCount int,
    repairPart varchar(100));

    insert into @tempPart (RowNum,repairID ,modelNumber ,repairDate ,loopCount,repairPart)
    select ROW_NUMBER() OVER(ORDER BY lr.repairDate, lpt.repairPart) RN1,
    lr.repairID,lr.modelNumber,lr.repairDate,
    (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
    ,ls.statusID, lpt.repairPart

    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.repairPart=pt.repairPart
    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

     

    Sunday, September 23, 2018 6:00 AM

All replies

  • User364663285 posted

    Do you mean that you check CarBrandID? Do you have index on it? If not, you can create it.

    Sunday, September 23, 2018 3:45 PM