1
\$\begingroup\$

I expanded the query provided by Gordon Linoff here.

I used this query, configured for my invoices, and it worked great - it took around 1.3 seconds to run. However, when configured as below for my Purchase Order data, it never returns a result timing out while the query runs. What optimization can I do to make this code (or something with the same desired result) run faster?

Desired result:

Customer name (database ()), number of orders in each range, total dollar amount, avg number of approvals for this range.


SELECT DATABASE() as PURCHASING_Count_Range, 
(case WHEN (total =0) then '$0' WHEN (total < 500) then 'Less than 
$500' WHEN (total >= 500) AND (total < 1000) then 'Between $500 and 
$1,000' WHEN (total >= 1000) AND (total < 10000) then 'Between $1,000 
and $10,000' WHEN (total >= 10000) and (total<=100000) then 'Between 
$10K and $100K' WHEN (total >10000.01) and (total <= 100000) then 
'Between $10K and $100K' WHEN (total >= 100000.01) then 'Over 
$100K'end) 
as 'Range', 

count(*) as Number_of_Reqs, 
SUM(case WHEN total =0 then total WHEN total < 500 then total WHEN 
total >= 500 AND total < 1000 then total WHEN total >= 1000 AND total < 
10000 then total WHEN total >= 10000 and total <=100000 then total WHEN 
total>100000.01 then total end)
as 'VALUE', 

(COUNT(app.approvable_id)/Count(*)) as AVG_Approvals 

FROM (select requisition_lines.header_id, sum(reporting_total) as total    
FROM requisition_lines LEFT JOIN requisition_headers ON 
(requisition_lines.header_id = requisition_headers.id) 

GROUP BY requisition_lines.header_id) requisition_lines  
LEFT JOIN requisition_headers ON 
(requisition_lines.header_id=requisition_headers.id =   
requisition_headers.id) 

LEFT JOIN (SELECT COUNT(approver_id) num_of_approvals, approvable_id 

FROM approvals where status = 'approved' group by approvable_id) app ON 
(requisition_headers.id=app.approvable_id) 

WHERE (requisition_headers.status NOT LIKE '%draft%') 
GROUP BY(case WHEN (total =0) then '$0' WHEN (total < 500) then 'Less 
than $500' WHEN (total >= 500) AND (total < 1000) then 'Between $500  
and $1,000' WHEN (total >= 1000) AND (total < 10000) then 'Between 
$1,000 and $10,000'WHEN (total >= 10000) and (total<=100000) then 
'Between $10K and $100K'WHEN (total >10000.01) and (total <= 100000) 
then 'Between $10K and $100K' WHEN (total >= 100000.01) then 'Over 
$100K'end)
\$\endgroup\$
  • 1
    \$\begingroup\$ Please describe the database schema and any indexes that might exist. \$\endgroup\$ – 200_success Feb 24 '16 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.