0

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.

Improve this question
9
  • Are you updating statistics on your database? there are many warnings in the execution plan on missing statistics.
    – kevinnwhat
    Commented Oct 10, 2019 at 18:21
  • auto create stats and auto update stats was off, I think that was something that azure did, so they could change stuff, but I turned it on now. I updated the Paste The Plan
    – DalTron
    Commented Oct 10, 2019 at 18:32
  • Is this the actual or estimated execution plan? It looks like the estimated, if so could you share the actual execution plan?
    – kevinnwhat
    Commented Oct 10, 2019 at 18:45
  • how do I show the actual? To get this plan I put SET SHOWPLAN_XML ON GO
    – DalTron
    Commented Oct 10, 2019 at 18:46
  • In SSMS there is a button to enable the actual execution plan, then when you execute the query, there will be an extra tab in the results grid called Execution plan, that's the actual plan. See here learn.microsoft.com/en-us/sql/relational-databases/performance/…
    – kevinnwhat
    Commented Oct 10, 2019 at 18:51

1 Answer 1

Reset to default
1

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)
Improve this answer
10
  • Thanks for the reply! I haven't messed with temp tables much, so I apologize if I am getting stuff super weird, but here is the plan to the INSERT SELECT brentozar.com/pastetheplan/?id=B1biBeC_B I couldn't get the SELECT INTO to create a XML plan, even an estimated one. I still don't know why I get an error when I paste the actual plan into Paste The Plan
    – DalTron
    Commented Oct 11, 2019 at 12:33
  • @DalTron No need to apologize :). Yeah select into does not really work with estimated plans. Could you run the query with the temp tables with SET STATISTICS TIME ON; (just run it before executing the query). That way we will know if the slowness is on the temp table insert or the second actual query. You will then get the messages tab filled with a lot of stuff, the timing is the interesting part here
    – Randi Vertongen
    Commented Oct 11, 2019 at 12:41
  • @DalTron These scalar functions are also going to be troublesome. Rewriting them to inline table valued functions could work, or adding them to the query itself
    – Randi Vertongen
    Commented Oct 11, 2019 at 12:46
  • @DalTron I added an example on the EDIT on trying to remove the scalar function, let me know if it works since I am not entirely sure
    – Randi Vertongen
    Commented Oct 11, 2019 at 12:52
  • I've tried to write the scalar functions inline, but I don't know if I just don't know the best way to do it, but it made the query slower. I'll try your outer apply suggestion in a second. I ran the queries with the Statistics and the SQL Server Execution Times on both queries is pretty high, the temp table is about half of the actual query
    – DalTron
    Commented Oct 11, 2019 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.