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

Possible slow query on user gradebook (grade/report/user/lib.php)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.9.3
    • Fix Version/s: None
    • Component/s: Gradebook, Performance
    • Labels:
      None
    • Affected Branches:
      MOODLE_39_STABLE

      Description

      Hi, there is a possible slow query on user gradebook (grade/report/user/lib.php)

      grade/report/user/lib.php seems inefficient on  sql statement 

       "SELECT gg.itemid, SUM(gg.finalgrade) AS sum
      FROM {grade_items} gi
      JOIN {grade_grades} gg ON gg.itemid = gi.id
      JOIN {user} u ON u.id = gg.userid
      JOIN ($enrolledsql) je ON je.id = gg.userid
      JOIN (
      SELECT DISTINCT ra.userid
      FROM {role_assignments} ra
      WHERE ra.roleid $gradebookrolessql
      AND ra.contextid $relatedctxsql
      ) rainner ON rainner.userid = u.id
      $groupsql
      WHERE gi.courseid = :courseid
      AND u.deleted = 0
      AND gg.finalgrade IS NOT NULL
      AND gg.hidden = 0
      $groupwheresql
      GROUP BY gg.itemid"

       

      In a case with 550 grade items  and 2200 participants mysql is marking this query as slow on logs and DB is affected for execution. Sql is trying to calculate sum of each item and this takes time. Maybe an index for finalgrade on grade_grades table is needed and also affects user gradebook page load time.

      Moodle 3.9.2 I tried to create an index on finalgrade and query became faster or somehow query can be optimized. 

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            tello Mahmut TEKER
            Participants:
            Component watchers:
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan, Matteo Scaramuccia, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated: