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

Data request grade provider creates long running query

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 3.5.2
    • Fix Version/s: None
    • Component/s: Privacy
    • Labels:
      None
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_35_STABLE

      Description

      When a student makes a data request, the grade privacy provider creates a long running query when adding the grades and modified grades, along with history.  The query uses left joins on both the grade and grade history table, however, it also excludes records records that are not in those tables in the final query.  Changing these left joins to inner joins makes the query run quickly, even on tables with 2+ million records. The code can be found in <MOODLE>/grade/classes/privacy/provider.php lines 207-224.  The proposed fix changes this code to 

       $sql = "
      SELECT DISTINCT ctx.id
      FROM {grade_items} gi
      JOIN {context} ctx
      ON ctx.instanceid = gi.courseid
      AND ctx.contextlevel = :courselevel
      JOIN (
      SELECT a.id, a.itemid FROM {grade_grades} a WHERE a.userid = :userid1 OR a.usermodified = :userid2
      UNION
      SELECT b.id, b.itemid FROM {grade_grades_history} b WHERE b.userid = :userid3 OR b.loggeduser = :userid4 OR b.usermodified = :userid5
      ) gg ON gi.id = gg.itemid";

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                amystewart Amy Stewart
                Participants:
                Component watchers:
                Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze
              • Votes:
                6 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: