Running a Course Participation report can bring down a Moodle site, if the log table is large enough. On an instance with ~18.8 million rows in the log table, the entire site slows to a crawl and then flicks over to giving the "Error: Database connection failed" error screen (screenshot attached).

I've been able to improve the performance considerably by removing the join to the log table in the query that's only used for counting users (as it was completely unnecessary), and switching the queries to use subqueries to filter large tables before joining them to other tables, rather than joining to the entire table and then filtering the result of the join. On my test environment, I managed to go from ~15 seconds to run a report to <0.5 seconds.

Patches attached for both 1.9.9 and 2.0 - it could probably be backported into earlier versions easily also (I haven't tried). The patches use subqueries for both the log table and the role_assignments table, though the performance gain on the latter may be negligible unless you have many thousands of users, each enrolled in many, many courses...