[RESOLVED] Query Timing out

treds1

I have a query to return data to produce a report showing month by month comparisons over 2 years for 80 centres. However, the query takes a unacceptably long time to run, and times out on most occasions. Bellow is an example of the statement, im sure there is a more efficient syntax for this query, but have as yet been unable to produce one.

Any ideas?

SELECT r.region_name, 
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=1 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover1,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=2 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover2,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=3 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover3,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=4 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover4,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=5 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover5,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=6 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover6,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=7 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover7,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=8 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover8,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=9 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover9,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=10 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover10,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=11 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover11,
(SELECT monthly_return_turnover FROM monthly_returnview WHERE monthly_return_month=12 AND monthly_return_year=2009 AND monthly_return_region_id = r.region_id) AS turnover12 
FROM monthly_returnview m INNER JOIN regionview r ON m.monthly_return_region_id = r.region_id GROUP BY r.region_id ORDER BY r.region_name

Cheers

Nick

Roger_Ramjet

Google for 'why cartesian joins are bad' and you'll find out why this query is taking so long.

This could be overcome by making all those individual queries into 1 UNION query, but that is still not the solution.

What you want here is a GROUP BY query using the aggregate functions to return the turnover by month and region, or region and month if you prefer 🙂

treds1

Thanks for your help, i knew there must be much easier way of doing this, as usual i was over complicating things.

The query now takes ms not minutes

Thanks again

nick