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

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

    XMLWordPrintable

    Details

    • Testing Instructions:
      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.
    • Affected Branches:
      MOODLE_35_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_310_STABLE
    • Pull Master Branch:
      master_MDL-69190

      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

              Assignee:
              danmarsden Dan Marsden
              Reporter:
              danmarsden Dan Marsden
              Peer reviewer:
              Peter Dias
              Integrator:
              Andrew Nicols
              Tester:
              Andrew Nicols
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan, Matteo Scaramuccia, Andrew Nicols, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                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