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

Bad performance of the Assign local roles screen

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.6.5, 2.7.2
    • Fix Version/s: 2.6.6, 2.7.3
    • Component/s: Roles / Access
    • Labels:
    • Testing Instructions:
      Hide

      Ideally, test this in a system with ~500,000 each of users, user_enrolments and role_assignments, and a course with ~10,000 enrolled users. However, you may just settle for testing that this does not cause regressions on a small site.

      1. Create a course
      2. Enrol some users
      3. Go into the news forum
      4. Go to Forum administration -> Locally assigned roles.
      5. Choose a role to assign.
      6. Verify that the page loads in a reasonable amount of time, showing the right users.
      7. Verify that you can assign and unassign roles.
      Show
      Ideally, test this in a system with ~500,000 each of users, user_enrolments and role_assignments, and a course with ~10,000 enrolled users. However, you may just settle for testing that this does not cause regressions on a small site. Create a course Enrol some users Go into the news forum Go to Forum administration -> Locally assigned roles. Choose a role to assign. Verify that the page loads in a reasonable amount of time, showing the right users. Verify that you can assign and unassign roles.
    • Affected Branches:
      MOODLE_26_STABLE, MOODLE_27_STABLE
    • Fixed Branches:
      MOODLE_26_STABLE, MOODLE_27_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      Sometimes (but not always) we are seeing really bad performance from the admin/roles/assign.php page (Locally assigned roles).

      It is not just having many rows in mdl_users, nor just having many users enrolled in a course, but some combinations of these seem to lead to the database executing this query in a very suboptimal way.

      The problem query comes from class core_role_potential_assignees_below_course in admin/roles/classes/potential_assignees_below_course.php, which is using get_enrolled_sql from lib/accesslib.php to build the subquery.

      It typically looks like:

      SELECT count(u.id)
        FROM mdl_user u
        LEFT JOIN mdl_role_assignments ra ON (ra.userid = u.id AND ra.roleid = 11 AND ra.contextid = 229431) 
       WHERE u.id IN (
          SELECT DISTINCT eu1_u.id
            FROM mdl_user eu1_u JOIN mdl_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id 
            JOIN mdl_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = 100071) 
           WHERE eu1_u.deleted = 0 AND eu1_u.id <> 1)
        AND u.id <> 1
        AND u.deleted = 0
        AND u.confirmed = 1
        AND ra.id IS NULL
      

        Attachments

          Activity

            People

            Assignee:
            timhunt Tim Hunt
            Reporter:
            timhunt Tim Hunt
            Peer reviewer:
            Russell Smith
            Integrator:
            Dan Poltawski
            Tester:
            Ankit Agarwal
            Participants:
            Component watchers:
            Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              10/Nov/14