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

Calculated Grade Items can cause high load in large courses

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 2.2, 2.3.2
    • Fix Version/s: None
    • Component/s: Gradebook
    • Labels:
      None
    • Testing Instructions:
      Hide

      1. Create a course
      2. Enrol 1000 students
      3. Create 100 grade items
      4.

      Show
      1. Create a course 2. Enrol 1000 students 3. Create 100 grade items 4.
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE

      Description

      Relevant Function: grade_item->compute

      The following query (see below) is run once for every calculated grade item in a course, for every change that is made to the gradebook. This means if you have 17 calculated grade items, and update 10 entries, this code will be run 170 times. When there are a large number of grade items in a course (our course has 134 including categories), and/or a large number of students (663), this query causes a large number of rows to be examined every time it's run (slow query log indicates around 64000).

      SELECT DISTINCT go.userid
      FROM

      {grade_grades} go
      JOIN {grade_items} gi
      ON (gi.id = go.itemid AND gi.courseid = ?)
      LEFT OUTER JOIN {grade_grades}

      g
      ON (g.userid = go.userid AND g.itemid = ?)
      WHERE gi.id <> ? AND g.id IS NULL

      This query is responsible for finding users that do not already have a grade_item for this calculated item, and creating it. Using that knowledge I've created the attached patch, which not only speeds up the SQL, by replacing it with comparable but different queries, but also reduces the number of times it runs, since it really only needs to run once per calculated grade item NOT once per grade item per grade updated (and it could even be optimized more, but this is more than enough for now).

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              andyjdavis Andrew Davis
              Reporter:
              jmlsteele Jody Steele
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: