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

Unwanted roles query slows down cron

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.1, 2.2
    • Fix Version/s: 2.1.5, 2.2.2
    • Component/s: Cohorts
    • Labels:
    • Database:
      MySQL
    • Testing Instructions:
      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.
    • Workaround:
      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 )
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      w04_MDL-30509_m22_slowcohort

      Description

      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.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    12/Mar/12