-
Bug
-
Resolution: Fixed
-
Minor
-
3.5.12, 3.7.6, 3.8.3, 3.9
-
MOODLE_35_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
-
MOODLE_310_STABLE
-
master_
MDL-69190 -
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 |
- will help resolve
-
-
- Closed
-
