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

Query for Identifying Grade Grades to Precreate is SLOW

    Details

    • Database:
      MySQL
    • Testing Instructions:
      Hide

      This will need testing in mysql and postgres at a minimum.

      Go to the categories and items screen within the gradebook of a course.

      Create a calculated grade item. Make its value equal to another activity. For example if you have a gradeable activity with the label "myactivity" set the calculated to "=[[myactivity]]"

      Save and go to the grader report.

      Override the gradeable activity, the one that the calc item relies upon. Check that you don't get an error and that the calc item has the same value as the original grade item.

      Repeat in other databases.

      For bonus points run "phpunit grade_item_testcase lib/grade/tests/grade_item_test.php"

      Show
      This will need testing in mysql and postgres at a minimum. Go to the categories and items screen within the gradebook of a course. Create a calculated grade item. Make its value equal to another activity. For example if you have a gradeable activity with the label "myactivity" set the calculated to "=[ [myactivity] ]" Save and go to the grader report. Override the gradeable activity, the one that the calc item relies upon. Check that you don't get an error and that the calc item has the same value as the original grade item. Repeat in other databases. For bonus points run "phpunit grade_item_testcase lib/grade/tests/grade_item_test.php"
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE, MOODLE_24_STABLE
    • Pull Master Branch:
      MDL-29733_master

      Description

      When a large number of students were taking a quiz yesterday, the grade_item::compute() function was being triggered on a fairly regular basis. The query to identify the grade_grades to precreate took an exceedingly long time to execute and used up our available connection pool.

      The query in question joins the monolithic grade_grades table with itself in an optional (LEFT OUTER) fashion which limits the types of query optimizations available. To avoid this, instead of using DISTINCT to achieve uniqueness while also making the optional join unnecessary, I would propose using the available index to GROUP BY userid and identify within an aggregation function whether the user meets the criteria.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

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

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    14/Jan/13