get_user_capability_course is really horribly inefficient. Here is what it currently does:
1) Gets all course records (id only) in one query
2) Gets all contexts for those courses in individual queries (get_context_instance)
3) Calls has_capability on each context and adds the resulting records to the array
I needed to use this function but also wanted to obtain some details from the course record (name, basically) and sort the records. The only easy way to do this was get_record for each resulting course, then php to sort. On a test system with approx 1k courses, this takes approx 5k queries for admin or approx 7k for a normal user.
Without a fundamental change to the function, i.e. it still goes through every course and calls has_capability, I intend to change the function to:
a) Obtain contexts as well as course records in the first query. Use a recordset instead of array so that we don't necessarily have to store every course in memory (only important if the user adds many fields).
b) Support $fields, $orderby to obtain additional fields from the course table, and sorting on that table, without extra queries or CPU work.
This reduces the query requirement to approx 1800 for an admin user and approx 3600 for a normal user.
I have attached my new version of the function (just as text not a patch - it's a drop-in replacement for the whole function).