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

Course gradebook slow query due to cross join on full user table.

    XMLWordPrintable

Details

    • MOODLE_35_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • MOODLE_310_STABLE
    • master_MDL-69190
    • Hide
      1. Enable debugging
      2. Enable perf debugging
      3. Create a Large course (Site admin -> Development -> Make test course)
      4. Navigate to the course -> Grades
      5. Refresh the page a couple of times
      6. Note the DB query time in the footer
      7. Open a terminal and find the commit:

        git log --oneline --grep="MDL-69190"
        

      8. Grab the commit hash and revert the commit, i.e.

        git revert 68115bf9870
        

      9. Navigate to the course -> Grades again
      10. Refresh the page a couple of times
        1. Confirm that the times are slower than the times noted earlier

      Please test in all supported databases.

      Show
      Enable debugging Enable perf debugging Create a Large course (Site admin -> Development -> Make test course) Navigate to the course -> Grades Refresh the page a couple of times Note the DB query time in the footer Open a terminal and find the commit: git log --oneline --grep="MDL-69190" Grab the commit hash and revert the commit, i.e. git revert 68115bf9870 Navigate to the course -> Grades again Refresh the page a couple of times Confirm that the times are slower than the times noted earlier Please test in all supported databases.

    Description

      The load time for the grade report (report/grader/index.php?) for large courses is quite slow.

      seeing the following query as taking longer than 3 seconds being the main culprit:

      SELECT gi.id, COUNT(DISTINCT u.id) AS count
        FROM mdl_grade_items gi
        CROSS JOIN mdl_user u
        JOIN (SELECT DISTINCT eu5_u.id
        FROM mdl_user eu5_u
        JOIN mdl_user_enrolments ej5_ue ON ej5_ue.userid = eu5_u.id
        JOIN mdl_enrol ej5_e ON (ej5_e.id = ej5_ue.enrolid AND ej5_e.courseid = $1)
         WHERE 1 = 1 AND eu5_u.deleted = 0) je  ON je.id = u.id
        JOIN mdl_role_assignments ra  ON ra.userid = u.id
        LEFT OUTER JOIN mdl_grade_grades g ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
      WHERE gi.courseid = $2  AND ra.roleid = $3  AND ra.contextid IN ($4,$5,$6,$7)                           AND u.deleted = 0 AND g.id IS NULL
      GROUP BY gi.id
      

      Here's where it's pulled through:
      https://github.com/moodle/moodle/blob/master/grade/report/grader/lib.php#L1532

      The cross join on the full mdl_user table seems to be the issue - re-arraning it to a cross join on the enrolled users like this seems to fix it:

      SELECT gi.id, COUNT(DISTINCT je.id) AS count
      FROM mdl_grade_items gi
      CROSS JOIN (SELECT DISTINCT eu5_u.id
      	      FROM mdl_user eu5_u
                    JOIN mdl_user_enrolments ej5_ue ON ej5_ue.userid = eu5_u.id
                    JOIN mdl_enrol ej5_e ON (ej5_e.id = ej5_ue.enrolid AND ej5_e.courseid = 8075)
                    JOIN mdl_role_assignments ra ON ra.userid = eu5_u.id
      	     WHERE 1 = 1 AND eu5_u.deleted = 0 AND ra.roleid = '5'
      	           AND ra.contextid IN ('1625131','8009','8008','1')) je
      LEFT OUTER JOIN mdl_grade_grades g ON (g.itemid = gi.id AND g.userid = je.id AND g.finalgrade IS NOT NULL)
      WHERE gi.courseid = 8075
      AND g.id IS NULL
      GROUP BY gi.id
      

      Attachments

        Issue Links

          Activity

            People

              danmarsden Dan Marsden
              danmarsden Dan Marsden
              Peter Dias Peter Dias
              Andrew Lyons Andrew Lyons
              Andrew Lyons Andrew Lyons
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                9/Nov/20

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 hours, 35 minutes
                  2h 35m