Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-9382

get_user_capability_course is horrendously inefficient

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.8.5, 1.9
    • Fix Version/s: 1.9
    • Labels:
      None
    • Database:
      Any
    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      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).

        Attachments

          Activity

            People

            • Assignee:
              skodak Petr Skoda
              Reporter:
              quen Sam Marshall
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Matteo Scaramuccia, Jake Dallimore, Jun Pataleta, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón
            • Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                3/Mar/08