Sql-server – Optimize Large Query Performance

azureperformancequery-performancesql server

I have this query that I can't seem to speed up at all. My running theory is that I need to fix some indexes or something.

I am running the database through the Azure and that does auto indexes, so I haven't had a whole lot of experience with indexes.

When I try to take stuff away, to see if it goes any faster, I get a little bit of speed. But when I take anything important that would probably speed everything up, I need to take out part of the WHERE statement and then I end up with a crap ton more rows and the speed is way worse.

So, here is my Paste The Plan

If anyone has any suggestions I would love to hear them.

Thanks in advance.

Best Answer

Since only the estimated plan is present, these are going to be some guesses.

This part

WHERE j.Status >= 60 AND (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

Creates a filter far in the execution plan:

enter image description here

on this part:

 (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

You could try adding a union to split them up, but due to the amount of joins I would try using a temp table to split some logic in two pieces.

I don't know your datatypes so I am going to use a SELECT ... INTO. Consider changing this to a INSERT ... SELECT .

Temp table insert:

  SELECT  j.JobId, 
        j.OrderId, 
        j.OrderType, 
        j.ShopName, 
        j.ShipDate, 
        j.CompletionDate, 
        j.InstallDate, 
        j.DayPriority,
        j.ShipDateConfirmed, 
        j.IsAddon, 
        j.CashOnDelivery, 
        j.FinalMeasure, 
        ds.ShopLabel, 
        ds.DesignSetId,
        --dsp.DesignSetProcessId, 
        j.PaintBottleneck, 
        j.DoorBottleNeck,
        j.AssemblyBottleNeck,
        --Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --      dsp.DatetimeStarted, 
        --dsp.DatetimeComplete, 
        --dsp.BatchNumber,
        --p.Name AS ProcessName, 
        --p.ProcessId, 
        --p.ZoneId,
        --X.CabinetCount, 
        j.InstallScheduled,
        j.RedTicket, 
        j.InstallDateRange, 
        --V.VitalItems, 
        --NV.NonVitalItems, 
        --Pj.ParentJobInstalled, 
        Schedule.WorkdaysByDate(j.ShipDate, j.InstallDate) AS ShipInstallDiff,
        dss.CatalogId AS SpeciesId, 
        dsld.CatalogId AS LowerDoorId, 
        dsupd.CatalogId AS UpperDoorId, 
        dsf.CatalogId AS FrontId, 
        dsfs.CatalogId AS FinishId,
        dsg.CatalogId AS GlazeId, 
        dsfo.CatalogId AS FinishOptionId, 
        dsds.CatalogId AS DistressId, 
        dssh.CatalogId AS SheenId, 
        dsi.CatalogId AS InteriorId,
        dsh.CatalogId AS HingeId, 
        dsd.CatalogId AS DrawerId, 
        dssl.CatalogId AS SlideId,
        j.ParentJobId --added for outer apply
INTO #temp
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
JOIN Species dss ON ds.DesignSetId = dss.DesignSetId 
JOIN LowerDoors dsld ON ds.DesignSetId = dsld.DesignSetId 
JOIN UpperDoors dsupd ON ds.DesignSetId = dsupd.DesignSetId
JOIN Fronts dsf ON ds.DesignSetId = dsf.DesignSetId 
JOIN Finishes dsfs ON ds.DesignSetId = dsfs.DesignSetId 
JOIN Glazes dsg ON ds.DesignSetId = dsg.DesignSetId
JOIN FinishOptions dsfo ON ds.DesignSetId = dsfo.DesignSetId 
JOIN Distresses dsds ON ds.DesignSetId = dsds.DesignSetId 
JOIN Sheens dssh ON ds.DesignSetId = dssh.DesignSetId
JOIN Interiors dsi ON ds.DesignSetId = dsi.DesignSetId 
JOIN Hinges dsh ON ds.DesignSetId = dsh.DesignSetId 
JOIN Drawers dsd ON ds.DesignSetId = dsd.DesignSetId
JOIN Slides dssl ON ds.DesignSetId = dssl.DesignSetId
WHERE j.Status >= 60;

You could then change the query to this

SELECT  temp.JobId, 
        temp.OrderId, 
        temp.OrderType, 
        temp.ShopName, 
        temp.ShipDate, 
        temp.CompletionDate, 
        temp.InstallDate, 
        temp.DayPriority,
        temp.ShipDateConfirmed, 
        temp.IsAddon, 
        temp.CashOnDelivery, 
        temp.FinalMeasure, 
        temp.ShopLabel, 
        temp.DesignSetId,
        dsp.DesignSetProcessId, 
        temp.PaintBottleneck, 
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted, 
        dsp.DatetimeComplete, 
        dsp.BatchNumber,
        p.Name AS ProcessName, 
        p.ProcessId, 
        p.ZoneId,
        X.CabinetCount, 
        temp.InstallScheduled,
        temp.RedTicket, 
        temp.InstallDateRange, 
        V.VitalItems, 
        NV.NonVitalItems, 
        Pj.ParentJobInstalled, 
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId, 
        temp.CatalogId AS LowerDoorId, 
        temp.CatalogId AS UpperDoorId, 
        temp.CatalogId AS FrontId, 
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId, 
        temp.CatalogId AS FinishOptionId, 
        temp.CatalogId AS DistressId, 
        temp.CatalogId AS SheenId, 
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId, 
        temp.CatalogId AS DrawerId, 
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj
WHERE  (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

Apart from this your functions are also going to hurt, depending on how much data is returned. enter image description here

You could add this index:

CREATE INDEX IX_Calendar_DateId_filtered
ON Schedule.Calendar(DateId)
INCLUDE(IsWorkDay)
WHERE IsWorkDay = 1;

The rest of the indexing is hard to say with an estimated plan, there are index posbilities such as:

CREATE INDEX IX_Jobs_Status_JobID
ON Jobs(Status,JobId)
INCLUDE(
        OrderId, 
        OrderType, 
        ShopName, 
        ShipDate, 
        CompletionDate, 
        InstallDate, 
        DayPriority,
        ShipDateConfirmed, 
        IsAddon, 
        CashOnDelivery, 
        FinalMeasure, 
        PaintBottleneck, 
        DoorBottleNeck,
        AssemblyBottleNeck,
        InstallScheduled,
        RedTicket, 
        InstallDateRange,
        ParentJobId
        );

CREATE INDEX IX_DesignSets
ON DesignSets(JobId,DesignSetId)
INCLUDE(ShopLabel);

But I think that getting the query to apply predicates earlier and/or splitting up the work will have bigger results at the moment.

EDIT

You could try changing the scalar functions to APPLY's. Since you have many apply's already you could add the first function as two apply's on the temp table insert:

SELECT  j.JobId, 
        j.OrderId, 
        j.OrderType, 
        j.ShopName, 
        j.ShipDate, 
        j.CompletionDate, 
        j.InstallDate, 
        j.DayPriority,
        j.ShipDateConfirmed, 
        j.IsAddon, 
        j.CashOnDelivery, 
        j.FinalMeasure, 
        ds.ShopLabel, 
        ds.DesignSetId,
        --dsp.DesignSetProcessId, 
        j.PaintBottleneck, 
        j.DoorBottleNeck,
        j.AssemblyBottleNeck,
        --Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --      dsp.DatetimeStarted, 
        --dsp.DatetimeComplete, 
        --dsp.BatchNumber,
        --p.Name AS ProcessName, 
        --p.ProcessId, 
        --p.ZoneId,
        --X.CabinetCount, 
        j.InstallScheduled,
        j.RedTicket, 
        j.InstallDateRange, 
        --V.VitalItems, 
        --NV.NonVitalItems, 
        --Pj.ParentJobInstalled, 
        ISNULL([WorkdaysByDate1],[WorkdaysByDate2])
        dss.CatalogId AS SpeciesId, 
        dsld.CatalogId AS LowerDoorId, 
        dsupd.CatalogId AS UpperDoorId, 
        dsf.CatalogId AS FrontId, 
        dsfs.CatalogId AS FinishId,
        dsg.CatalogId AS GlazeId, 
        dsfo.CatalogId AS FinishOptionId, 
        dsds.CatalogId AS DistressId, 
        dssh.CatalogId AS SheenId, 
        dsi.CatalogId AS InteriorId,
        dsh.CatalogId AS HingeId, 
        dsd.CatalogId AS DrawerId, 
        dssl.CatalogId AS SlideId,
        j.ParentJobId --added for outer apply
INTO #temp
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
JOIN Species dss ON ds.DesignSetId = dss.DesignSetId 
JOIN LowerDoors dsld ON ds.DesignSetId = dsld.DesignSetId 
JOIN UpperDoors dsupd ON ds.DesignSetId = dsupd.DesignSetId
JOIN Fronts dsf ON ds.DesignSetId = dsf.DesignSetId 
JOIN Finishes dsfs ON ds.DesignSetId = dsfs.DesignSetId 
JOIN Glazes dsg ON ds.DesignSetId = dsg.DesignSetId
JOIN FinishOptions dsfo ON ds.DesignSetId = dsfo.DesignSetId 
JOIN Distresses dsds ON ds.DesignSetId = dsds.DesignSetId 
JOIN Sheens dssh ON ds.DesignSetId = dssh.DesignSetId
JOIN Interiors dsi ON ds.DesignSetId = dsi.DesignSetId 
JOIN Hinges dsh ON ds.DesignSetId = dsh.DesignSetId 
JOIN Drawers dsd ON ds.DesignSetId = dsd.DesignSetId
JOIN Slides dssl ON ds.DesignSetId = dssl.DesignSetId
OUTER APPLY (SELECT SUM(IsWorkDay) AS Workdays
                    From Schedule.Calendar
                    Where DateId >= j.ShipDate AND DateId <= j.InstallDate) as [WorkdaysByDate1]
OUTER APPLY (Select SUM(IsWorkDay) AS Workdays
                            From Schedule.Calendar
                            Where DateId <= j.ShipDate AND DateId >= j.InstallDate) as [WorkdaysByDate2]
WHERE j.Status >= 60;

EDIT 2

So, when I remove the scalar functions, on my SELECT INTO query my temp table execution time is CPU time = 281 ms, elapsed time = 293 ms. and my query execution time is CPU time = 1969 ms, elapsed time = 3694 ms. On my INSERT INTO query my temp table execution time is CPU time 282 ms, elapsed time = 442 ms. and my query execution time is CPU time 1984 ms, elapsed time = 3883 ms.

Most of the execution time is on the second part, what happens when you write the second part of the query like this?

SELECT  temp.JobId, 
        temp.OrderId, 
        temp.OrderType, 
        temp.ShopName, 
        temp.ShipDate, 
        temp.CompletionDate, 
        temp.InstallDate, 
        temp.DayPriority,
        temp.ShipDateConfirmed, 
        temp.IsAddon, 
        temp.CashOnDelivery, 
        temp.FinalMeasure, 
        temp.ShopLabel, 
        temp.DesignSetId,
        dsp.DesignSetProcessId, 
        temp.PaintBottleneck, 
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted, 
        dsp.DatetimeComplete, 
        dsp.BatchNumber,
        p.Name AS ProcessName, 
        p.ProcessId, 
        p.ZoneId,
        X.CabinetCount, 
        temp.InstallScheduled,
        temp.RedTicket, 
        temp.InstallDateRange, 
        V.VitalItems, 
        NV.NonVitalItems, 
        Pj.ParentJobInstalled, 
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId, 
        temp.CatalogId AS LowerDoorId, 
        temp.CatalogId AS UpperDoorId, 
        temp.CatalogId AS FrontId, 
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId, 
        temp.CatalogId AS FinishOptionId, 
        temp.CatalogId AS DistressId, 
        temp.CatalogId AS SheenId, 
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId, 
        temp.CatalogId AS DrawerId, 
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj
WHERE (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))
UNION
SELECT  temp.JobId, 
        temp.OrderId, 
        temp.OrderType, 
        temp.ShopName, 
        temp.ShipDate, 
        temp.CompletionDate, 
        temp.InstallDate, 
        temp.DayPriority,
        temp.ShipDateConfirmed, 
        temp.IsAddon, 
        temp.CashOnDelivery, 
        temp.FinalMeasure, 
        temp.ShopLabel, 
        temp.DesignSetId,
        dsp.DesignSetProcessId, 
        temp.PaintBottleneck, 
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted, 
        dsp.DatetimeComplete, 
        dsp.BatchNumber,
        p.Name AS ProcessName, 
        p.ProcessId, 
        p.ZoneId,
        X.CabinetCount, 
        temp.InstallScheduled,
        temp.RedTicket, 
        temp.InstallDateRange, 
        V.VitalItems, 
        NV.NonVitalItems, 
        Pj.ParentJobInstalled, 
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId, 
        temp.CatalogId AS LowerDoorId, 
        temp.CatalogId AS UpperDoorId, 
        temp.CatalogId AS FrontId, 
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId, 
        temp.CatalogId AS FinishOptionId, 
        temp.CatalogId AS DistressId, 
        temp.CatalogId AS SheenId, 
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId, 
        temp.CatalogId AS DrawerId, 
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj

WHERE  (j.Status <= 70)
Related Question