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

Grade item and grade category optimisations

XMLWordPrintable

    • MySQL
    • MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_405_STABLE
    • MOODLE_405_STABLE
    • MDL-64325-main
    • Moderate
    • Hide

      Checking performance improvements manually is quite complicated because the following are required:

      • an instance with very large grade_items and grade_grades tables.
      • a quiz with a large number of quiz attempts.

      The compute (grade_item.php) and generate_grades (grade_category.php) methods are covered by PHPUnit tests.

       

       

      Show
      Checking performance improvements manually is quite complicated because the following are required: an instance with very large grade_items and grade_grades tables. a quiz with a large number of quiz attempts. The  compute (grade_item.php) and generate_grades (grade_category.php) methods are covered by PHPUnit tests.    
    • 3
    • Team Alpha - Planning I2-2024, Team Alpha - Sprint 1 I2-2024

      There is a significant performance bottle neck  in the compute function in grade_items 

      https://github.com/moodle/moodle/blob/2cea0bd6c66300813457a2f18dc9a4a075efbc93/lib/grade/grade_item.php#L2119

       

      The query looks like this

      $sql = "SELECT $fields
      FROM {grade_grades} g, {grade_items} gi
      WHERE gi.id = g.itemid AND gi.id $usql $usersql AND gi.courseid=?
      ORDER BY g.userid";

      and $usql contains a list of gradeitems

      No gradeitem fields are returned from the query and there appears to be no benefit from filtering by gi.courseid because
      we are already filtering by grade_items - the method that builds the list of grade_items ("depends_on") already
      filters the list of grade items to a specific course.

      If the query is reformulated as:

      $sql = "SELECT $fields
      FROM {grade_grades} g
      WHERE g.itemid $usql $usersql
      ORDER BY g.userid";

      then the execution time drops by a huge amount. This was discovered when calling the 
      quiz_delete_attempt($attempt, $quiz);
      In a utility and the performance made it unusable. 

       

      In grade_category.php we see exactly the same issue with the following SQL:

      $sql = "SELECT $fields
      FROM {grade_grades} g, {grade_items} gi
      WHERE gi.id = g.itemid AND gi.id $usql $usersql
      ORDER BY g.userid";

       

      Another optimisation - in grade_category.php, the generate_grades method makes a call to
      get grade items from a specific set of ids:

      $sql = "SELECT *
      FROM {grade_items}
      WHERE id $usql";
      $items = $DB->get_records_sql($sql, $params);
      foreach ($items as $id => $item)

      { $items[$id] = new grade_item($item, false); }

      The function quiz_delete_previews in mod/quiz/locallib.php calls quiz_delete_attempt in a
      loop.
      Since quiz_delete_attempt ultimately results in the 'generate_grades' method being called
      there is a risk of the same DB query being made repeatedly - this can be fixed using
      static caching where the ids haven't changed from the previous call.

      Outcome

      These optimisations were tested against a system with 2.5 million grade_grades and 648 grade_items. Deleting attempts was exceptionally slow, and caused timeouts on machines with low ram (4Gb). Deletion can be done via

      /mod/quiz/report.php?id=5&mode=responses

      These optimisations reduced the time from around 90 seconds to under 10 seconds.

       
       

       

       

            kevin.percy@moodle.com Kevin Percy
            marcusgreen Marcus Green
            Mihail Geshoski Mihail Geshoski
            Jun Pataleta Jun Pataleta
            CiBoT CiBoT
            Votes:
            3 Vote for this issue
            Watchers:
            13 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 week, 2 hours, 9 minutes
                1w 2h 9m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.