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

Unwanted roles query slows down cron

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Fixed
    • Icon: Minor Minor
    • 2.1.5, 2.2.2
    • 2.1, 2.2
    • Cohorts
    • MySQL
    • MOODLE_21_STABLE, MOODLE_22_STABLE
    • MOODLE_21_STABLE, MOODLE_22_STABLE
    • w04_MDL-30509_m22_slowcohort
    • Hide

      This is the query I've re-written which works much faster in MySQL (and as far as I can tell, gives the same result):

              SELECT ra.roleid, ra.userid, ra.contextid, ra.itemid
              FROM {role_assignments} ra
              JOIN {context} c ON ( c.id = ra.contextid AND c.contextlevel = :coursecontext $onecourse )
              WHERE ra.component = 'enrol_cohort'
              AND NOT EXISTS (
                  SELECT e.id
                  FROM {user_enrolments} ue
                  JOIN {enrol} e ON ( e.id = ue.enrolid AND e.enrol = 'cohort' )
                  WHERE e.id = ra.itemid
                  AND roleid = ra.roleid
                  AND ue.userid = ra.userid
              )

      Show
      This is the query I've re-written which works much faster in MySQL (and as far as I can tell, gives the same result): SELECT ra.roleid, ra.userid, ra.contextid, ra.itemid FROM {role_assignments} ra JOIN {context} c ON ( c.id = ra.contextid AND c.contextlevel = :coursecontext $onecourse ) WHERE ra.component = 'enrol_cohort' AND NOT EXISTS ( SELECT e.id FROM {user_enrolments} ue JOIN {enrol} e ON ( e.id = ue.enrolid AND e.enrol = 'cohort' ) WHERE e.id = ra.itemid AND roleid = ra.roleid AND ue.userid = ra.userid )
    • Hide

      1/ enable cohort enrol plugin
      2/ create a script that executes enrol_cohort_sync(); from require_once("$CFG->dirroot/enrol/cohort/locallib.php");
      3/ no errors expected

      It should not be necessary to test the complete enrol plugin because there are know problems that were only resolved in master.

      Show
      1/ enable cohort enrol plugin 2/ create a script that executes enrol_cohort_sync(); from require_once("$CFG->dirroot/enrol/cohort/locallib.php"); 3/ no errors expected It should not be necessary to test the complete enrol plugin because there are know problems that were only resolved in master.

      A site that has thousands of users enrolled by cron synch has flagged a significant delay in the cron with the query to "remove unwanted roles" in enrol/cohort/locallib.php function enrol_cohort_sync()

      It does admit to "take a long time" during upgrade, but it does on every cron execution, and I think it can be a lot faster (less than 1/10th the time according to my testing in MySQL) by rewriting the outer join as a WHERE NOT EXISTS clause.

            skodak Petr Skoda
            yairspielmann Yair Spielmann
            Sam Hemelryk Sam Hemelryk
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.