Full Outer Joins Slowing Down Query

Question

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.

Answer 1

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;

Answer 2

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.