Sfrgttk

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.

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;