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.
Here is the link to paste the plan: https://www.brentozar.com/pastetheplan/?id=SytXw1dum
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.