AndyRoth avatar image
0 Votes"
AndyRoth asked Ehren commented

Query Performance

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
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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi there. This doesn't appear to be a Power Query question. I'd suggest you change the tag to reflect the question category, or post the question in a more relevant forum.

0 Votes 0 ·

0 Answers