1

I am relatively new to SQL, so I apologize if I ask some basic questions.

Our database is setup on Azure, we have automatic indexes turned on. The query I have takes about 6 seconds and gets 2217 rows. As far as I have been able to figure out is that it is my Full Outer Joins that are slowing down my query. I am fine if I can just get it down to 2~3 seconds.

Here is my query:

SELECT j.JobId, j.OrderId, j.OrderType, j.ShopName, j.ShipDate, j.CompletionDate, j.InstallDate, j.DayPriority,
        j.ShipDateConfirmed, j.IsAddon, j.CashOnDelivery, ds.ShopLabel, ds.DesignSetId, dsp.DesignSetProcessId, dsp.DueDate AS DesignSetProcessDueDate,
        dsp.DatetimeStarted, dsp.DatetimeComplete, dsp.BatchNumber, p.Name AS ProcessName, p.ProcessId, p.ZoneId, X.CabinetCount
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
FULL OUTER JOIN Schedule.DesignSetProcessesWithDueDates dsp ON ds.DesignSetId = dsp.DesignSetId
FULL OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount FROM Products pr WHERE ds.DesignSetId = pr.DesignSetId AND 
                                        (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
WHERE j.Status >= 60 AND j.Status <= 70
ORDER BY j.CompletionDate, j.DayPriority DESC, j.ShopName, j.OrderId, ds.ShopLabel, p.SortOrder, dsp.BatchNumber

This query gets a master view of the jobs and the processes within the job. All the information that I am getting is needed and all the rows are to.

Is there anything that I can use as a substitute for the Full Outer Joins?

If I can just get one of them to go faster I think the query would work better.

If you have any suggestion to how I can speed up the rest of my query I am willing to give it a shot.

EDIT

Here is the link to paste the plan: https://www.brentozar.com/pastetheplan/?id=SytXw1dum

UPDATE

Turns out one of the columns from a view was slowing everything down, so I got rid of dsp.DueDate AS DesignSetProcessDueDate and now it runs in less than a second. I was able to find out how that view was getting the information and put it right into my query.

  • I put my execution plan in there. If you need any more information just let me know. Thank you for responding. – DalTron Sep 12 '18 at 21:16
  • 1
    The graphical plan is not enough. use brentozar.com/pastetheplan/instructions – SQLRaptor Sep 12 '18 at 22:16
  • Thank you for the clarification, I have posted the link through pastetheplan.com and I have added a link to go view that. – DalTron Sep 13 '18 at 13:35
  • Are plans improved if you replace the two FULL joins with LEFT ones? – ypercubeᵀᴹ Sep 13 '18 at 13:48
  • No, they aren't. If I just do a join the speed is improved, but I also loose some rows. Though I didn't lose any rows when I did a LEFT join, so I guess it's not the Full Outer Join that is the problem – DalTron Sep 13 '18 at 14:09
0

updating answer to Use LEFT JOINS..

SELECT j.JobId
     , j.OrderId
     , j.OrderType
     , j.ShopName
     , j.ShipDate
     , j.CompletionDate
     , j.InstallDate
     , j.DayPriority
     , j.ShipDateConfirmed
     , j.IsAddon
     , j.CashOnDelivery
     , ds.ShopLabel
     , ds.DesignSetId
     , dsp.DesignSetProcessId
     , dsp.DueDate AS DesignSetProcessDueDate
     , dsp.DatetimeStarted
     , dsp.DatetimeComplete
     , dsp.BatchNumber
     , p.Name AS ProcessName
     , p.ProcessId
     , p.ZoneId
     , (
               SELECT SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount
               FROM Products pr
               WHERE ds.DesignSetId = pr.DesignSetId
                     AND (
                            dsp.BatchNumber = pr.BatchNumber
                           -- OR dsp.DesignSetId IS NULL    Note : Since dsp.DesignSetId = ds.DesignSetId this condition can be omitted. uncomment it if necessary.
                         )
            ) AS CabinetCount
FROM Jobs j
JOIN DesignSets ds
   ON j.JobId = ds.JobId
LEFT OUTER JOIN Schedule.DesignSetProcessesWithDueDates dsp
   ON ds.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p
   ON dsp.ProcessId = p.ProcessId
WHERE j.Status >= 60
      AND j.Status <= 70
ORDER BY j.CompletionDate
       , j.DayPriority DESC
       , j.ShopName
       , j.OrderId
       , ds.ShopLabel
       , p.SortOrder
       , dsp.BatchNumber;
  • Thanks for your answer, but unfortunately even if I remove the Outer Apply and the CabinetCount from my entire query it doesn't speed the query up at all. – DalTron Sep 13 '18 at 15:16
  • Next step would be to replace FULL OUTER JOIN with LEFT OUTER JOINS as mentioned in other comments. Since the DesignSets table is being joined with with jobs table only those records from DesignSets will qualify which have jobsid. Hence converting FULL Outer to Left should produce same result set – Unkush Sep 13 '18 at 15:48
  • replacing FULL OUTER JOIN with LEFT OUTER JOINS doesn't speed the query up. I think I need to do something like what @SEarle1986 suggested, but I am waiting for a coworker to get some time, so we can look at it. – DalTron Sep 13 '18 at 16:07
0

Posting this as an answer per @James Jenkins request

Turns out one of the columns from a view was slowing everything down, so I got rid of dsp.DueDate AS DesignSetProcessDueDate and now it runs in less than a second.

I was able to find out how that view was getting the information and put it right into my query. Which made it so I could get the same information in less than a second.

So, it looks like the answer was to find out where the data is coming from and see if that is causing the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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