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

Grade item and grade category optimisations

    XMLWordPrintable

    Details

      Description

      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.

       
       

       

       

        Attachments

          Activity

            People

            • Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated: