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

Profile page has incredibly poor performance when database contains many courses

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.8
    • Fix Version/s: 1.8
    • Component/s: Unknown
    • Labels:
      None
    • Database:
      Any
    • Affected Branches:
      MOODLE_18_STABLE
    • Fixed Branches:
      MOODLE_18_STABLE

      Description

      To reproduce, find a database with a few thousand courses in. Turn on performance tracking. Visit the profile page for:

      i) An arbitrary user who is a student on one or two courses
      ii) The admin user

      In case I saw around 16,000 queries. In case (ii) I saw around 48,000 queries. This results in unacceptably poor performance.

      The problem is caused by the function get_my_courses() which checks every single course on the system to see whether or not the user has access. My recommended solution, corresponding to the two cases above, would be:

      i) Provide optimisation that reduces the set of courses which get_my_courses must check. (This improves performance for most users by reducing the number of courses that need checking to a handful instead of 2,000.)

      ii) Provide an optional parameter to get_my_courses, which can be used from the profile page, that limits the maximum number of courses returned e.g. to 20 so that it doesn't use thousands of queries showing every course on the system.

      These changes reduce the number of queries for the profile page to 98 and 403 respectively on our system.

      I am working on a patch (which may use a Postgres-specific query so need modification) that fixes the issue but am currently unable to test this as my core Moodle HEAD install doesn't work at all. Will attach patch at such time as I get it working.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                quen Sam Marshall
                Reporter:
                quen Sam Marshall
                Tester:
                Nobody
                Participants:
                Component watchers:
                Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  31/Mar/07