CODEWITHSUNDEEP
sqlsubqueryquery-performance
Lewis Morris
Lewis Morris

Reputation: 2124

Making select query more efficient (subquery slows run speed)

The below query seems to take forever to run ever since I have added the subquery into it.

I originally tried to accomplish my goal by having two joins but the results were wrong.

Does anyone know the correct way to write this?

SELECT 
    c.cus_Name,
    COUNT(o.orderHeader_id) AS Orders,
    (select count(ol.orderLines_id) from orderlines ol where ol.orderLines_orderId = o.orderHeader_id) as linesOrderd,
    MAX(o.orderHeader_dateCreated) AS lastOrdered,
    SUM(o.orderHeader_totalSell) AS orderTotal,
    SUM(o.orderHeader_currentSell) AS sellTotal
FROM
    cus c
        JOIN
    orderheader o ON o.orderHeader_customer = c.cus_id
group by
    c.cus_name
order by 
    orderTotal desc

Example data below

Example Data of order header and order totals

Upvotes: 1

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

For the data you want, I think this is the way to go:

SELECT c.cus_Name,
       COUNT(o.orderHeader_id) AS Orders,
       SUM(ol.cnt) as linesOrderd,
       MAX(o.orderHeader_dateCreated) AS lastOrdered,
       SUM(o.orderHeader_totalSell) AS orderTotal,
       SUM(o.orderHeader_currentSell) AS sellTotal
FROM cus c JOIN
     orderheader o
     ON o.orderHeader_customer = c.cus_id LEFT JOIN
     (SELECT ol.orderLines_orderId, count(*) as cnt
      FROM orderlines ol
      GROUP BY ol.orderLines_orderId
     ) ol
     ON ol.orderLines_orderId = o.orderHeader_id)
GROUP BY c.cus_name
ORDER BY orderTotal DESC;

I'm not sure if it will be much faster, but it will at least produce a sensible result -- the total number of order lines for a customer rather than the number of order lines on an arbitrary order.

Upvotes: 1

aschoerk
aschoerk

Reputation: 3593

Strange that subselect should not be possible since the count is only very indirectly related to the grouping. You want to count all orderlines of all orders which are related to one customer? Normally this should be done using the second join, but then the orderheader will be repeated as often as the order_lines exist. That would produce wrong results in the other aggregations.

normally this should help then, put the subselect into the joined table:

could you replace orderheader o by

(select o.*, (select count(ol.orderLines_id) from orderlines ol where ol.orderLines_orderId = o.orderHeader_id) as linesOrder from orderheader o) as o

and replace the subselect by

sum(o.linesOrder)

Upvotes: 1

Related Questions