Query Performance

RuthlessRoth 1 Reputation point
2021-10-07T22:27:45.603+00:00

I am working in a database that I have no control over just reporting rights

I have a query that takes 37 seconds to run and returns 380 records. (all open jobs in the shop.

The problem is the subqueries I use to get hours required on each piece of equipment used for the jobs.

Each sub query adds 3-4 seconds to the result time.

Here is on of them. The difference is the cost center I am looking for and the name of the field
,IsNull((
SELECT Sum(OrderValue.ProductionMinutes) / 60
FROM OrderValue
INNER JOIN OrderProcess ON OrderValue.JobNumber = OrderProcess.JobNumber
AND OrderValue.ComponentNumber = OrderProcess.ComponentNumber
AND OrderValue.GroupNo = OrderProcess.GroupNo
AND OrderValue.ItemNumber = OrderProcess.ItemNumber
AND OrderValue.PartNumber = OrderProcess.PartNumber
INNER JOIN OrderQtyTable ON OrderValue.JobNumber = OrderQtyTable.JobNumber
AND OrderValue.ComponentNumber = OrderQtyTable.ComponentNumber
AND OrderValue.QuantityLineNo = OrderQtyTable.QuantityLineNo
LEFT JOIN Process ON OrderProcess.ProcessCode = Process.ProcessCode
INNER JOIN OrderComponent ON OrderValue.JobNumber = OrderComponent.JobNumber
AND OrderValue.ComponentNumber = OrderComponent.ComponentNumber
AND OrderValue.QuantityLineNo = OrderComponent.QtyOrdIndex
WHERE OrderProcess.JobNumber = OH.JobNumber
AND OrderProcess.PartNumber = 0
AND OrderProcess.DontPrintOnTicket = 0
AND Process.CostCenterCode = 4000
), 0) AS NexPRess

I have 19 of these. There has to be a faster way to do this.
Any suggestions how to speed this query up?

Thanks Andy

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,969 questions