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

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

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Waiting for peer review
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.5.12, 3.7.6, 3.8.3, 3.9
    • Fix Version/s: None
    • Component/s: Gradebook, Performance
    • Testing Instructions:
      Hide

      With performance logging turned on and in a site with large numbers of users and a course with a large amount of gradeitems, load the grade/report/grader/index.php page and make sure the page operates as expected.

      After the patch is applied - note a decrease in the time spent on SQL queries.

      Please test in all supported databases.

      Show
      With performance logging turned on and in a site with large numbers of users and a course with a large amount of gradeitems, load the grade/report/grader/index.php page and make sure the page operates as expected. After the patch is applied - note a decrease in the time spent on SQL queries. Please test in all supported databases.
    • Affected Branches:
      MOODLE_35_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_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

          Activity

            People

            Assignee:
            danmarsden Dan Marsden
            Reporter:
            danmarsden Dan Marsden
            Participants:
            Component watchers:
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: