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

Grade item and grade category optimisations




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



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


      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


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







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


              • Created: