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

Query slowness on grade_items lookup

XMLWordPrintable

    • MOODLE_24_STABLE, MOODLE_25_STABLE
    • MOODLE_24_STABLE, MOODLE_25_STABLE
    • MDL-42065_master
    • 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

      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

            sbc24 Sam Chaffee
            kstokking Kris Stokking (Inactive)
            Frédéric Massart Frédéric Massart
            Damyon Wiese Damyon Wiese
            Andrew Lyons Andrew Lyons
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved:

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