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

Query slowness on grade_items lookup

    Details

    • Testing Instructions:
      Hide

      Validate existing grades

      1. Before patch is applied:
        1. Create or re-use an existing course that has multiple grade items and categories.
        2. Ensure that at least one of the grade categories has aggregatesubcats enabled, and at least one has aggregatesubcats disabled
        3. Ensure that at least one of the grade categories has aggregateoutcomes enabled, and at least one has aggregateoutcomes disabled
        4. Ensure that there are grades present for multiple students
        5. Take screenshots of the gradebook for future verification
      2. After the patch is applied
        1. Visit the gradebook. Ensure the results are consistent before and after.

      Validate grading

      1. In one of the graded activities, enter a grade for a student
      2. In one of the graded activities, edit a grade for a student
      3. Force a regrade (e.g. move a grade item into a new category)
      4. Validate that the changes took effect and are properly aggregated in the gradebook
      Show
      Validate existing grades Before patch is applied: Create or re-use an existing course that has multiple grade items and categories. Ensure that at least one of the grade categories has aggregatesubcats enabled, and at least one has aggregatesubcats disabled Ensure that at least one of the grade categories has aggregateoutcomes enabled, and at least one has aggregateoutcomes disabled Ensure that there are grades present for multiple students Take screenshots of the gradebook for future verification After the patch is applied Visit the gradebook. Ensure the results are consistent before and after. Validate grading In one of the graded activities, enter a grade for a student In one of the graded activities, edit a grade for a student Force a regrade (e.g. move a grade item into a new category) Validate that the changes took effect and are properly aggregated in the gradebook
    • Affected Branches:
      MOODLE_24_STABLE, MOODLE_25_STABLE
    • Fixed Branches:
      MOODLE_24_STABLE, MOODLE_25_STABLE
    • Pull Master Branch:
      MDL-42065_master

      Description

      We are noticing significant slowness on a site that has a large number of total grade items (in some cases > 1,000 grade items in multiple courses). Both queries could be optimized to include the grade_category or grade_item courseid (both indexed).

      Query 1

      SELECT gi.id
      FROM mdl_grade_items gi
      WHERE (gi.gradetype = '1' OR gi.gradetype = '2')
      AND gi.outcomeid IS NULL
      AND gi.categoryid IN (
          SELECT gc.id
          FROM mdl_grade_categories gc
          WHERE gc.path LIKE '%/9820/%'
      )
      

      Query 2

      SELECT gi.id
      FROM mdl_grade_items gi
      WHERE (gi.gradetype = ? OR gi.gradetype = ?) 
      AND gi.categoryid = ? 
      AND gi.outcomeid IS NULL 
       
      UNION
       
      SELECT gi.id FROM mdl_grade_items gi, mdl_grade_categories gc 
      WHERE (gi.itemtype = ? OR gi.itemtype = ?) 
      AND gi.iteminstance=gc.id
      AND gc.parent = ? 
      AND (gi.gradetype = ? OR gi.gradetype = ?) 
      AND gi.outcomeid IS NULL
      

        Gliffy Diagrams

          Attachments

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  11/Nov/13