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

Sorting quiz attempts overview page by question causes extremely slow query

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.1.2, 2.2
    • Fix Version/s: 2.1.3
    • Component/s: Quiz
    • Labels:
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      1. Create a quiz that allows multiple attempts per students, and in separate groups mode (make sure there are several groups set up in the course). Make sure the quiz has at least several questions. Preferably more.

      2. Create many quiz attempts, as many different students.

      (Ideally, find a real quiz from a copy of a real Moodle site.)

      3. Then for many combinations of options
      a. Show / download
      b. Only show / download attempts
      c. Page size
      d. Include (for the responses report)
      d. Choices in the initials bar.
      e. Which group is selected (or all groups)
      f. Which column the table is sorted on. Focus in particular on sorting by individual question grade, or Question/response/right answer (depending on overview, or responses report.)

      4. Compare the report with and without this patch, and verify that it is identical.

      5. Do this for both the overview (grades) and responses report.

      Show
      1. Create a quiz that allows multiple attempts per students, and in separate groups mode (make sure there are several groups set up in the course). Make sure the quiz has at least several questions. Preferably more. 2. Create many quiz attempts, as many different students. (Ideally, find a real quiz from a copy of a real Moodle site.) 3. Then for many combinations of options a. Show / download b. Only show / download attempts c. Page size d. Include (for the responses report) d. Choices in the initials bar. e. Which group is selected (or all groups) f. Which column the table is sorted on. Focus in particular on sorting by individual question grade, or Question/response/right answer (depending on overview, or responses report.) 4. Compare the report with and without this patch, and verify that it is identical. 5. Do this for both the overview (grades) and responses report.
    • Workaround:
      Hide

      Altering the query such that the second left join called does not return so many results seems to get it.

      If you change the query above to the one below you get the same results, in a fraction of the time (query above reduced to 2 seconds. The additional line is on the second left join having it only include attempts for the quiz we are looking at as opposed to all quizzes. ' where qa7qa.questionusageid in (select uniqueid from mdl_quiz_attempts where quiz = '434' )

      SELECT
      CONCAT(u.id, '#', COALESCE(quiza.attempt, 0)) AS uniqueid,
      quiza.uniqueid AS usageid,
      quiza.id AS attempt,
      u.id AS userid,
      u.idnumber,
      u.firstname,
      u.lastname,
      u.picture,
      u.imagealt,
      u.institution,
      u.department,
      u.email,
      quiza.sumgrades,
      quiza.timefinish,
      quiza.timestart,
      CASE WHEN quiza.timefinish = 0 THEN null
      WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
      ELSE 0 END AS duration, COALESCE((
      SELECT MAX(qqr.regraded)
      FROM mdl_quiz_overview_regrades qqr
      WHERE qqr.questionusageid = quiza.uniqueid
      ), -1) AS regraded,
      qa7.fraction * qa7.maxmark AS qsgrade7
      FROM
      mdl_user u
      LEFT JOIN mdl_quiz_attempts quiza ON
      quiza.userid = u.id AND quiza.quiz = '434'
      LEFT JOIN (
      SELECT
      qa7qa.id AS questionattemptid,
      qa7qa.questionusageid,
      qa7qa.slot,
      qa7qa.behaviour,
      qa7qa.questionid,
      qa7qa.variant,
      qa7qa.maxmark,
      qa7qa.minfraction,
      qa7qa.flagged,
      qa7qa.questionsummary,
      qa7qa.rightanswer,
      qa7qa.responsesummary,
      qa7qa.timemodified,
      qa7qas.id AS attemptstepid,
      qa7qas.sequencenumber,
      qa7qas.state,
      qa7qas.fraction,
      qa7qas.timecreated,
      qa7qas.userid

      FROM mdl_question_attempts qa7qa
      JOIN mdl_question_attempt_steps qa7qas ON
      qa7qas.id = (
      SELECT MAX(id)
      FROM mdl_question_attempt_steps
      WHERE questionattemptid = qa7qa.id
      ) where qa7qa.questionusageid in (select uniqueid from mdl_quiz_attempts where quiz = '434' )
      ) qa7 ON qa7.questionusageid = quiza.uniqueid AND qa7.slot = '7'
      WHERE quiza.id IS NOT NULL AND quiza.preview = 0
      ORDER BY qsgrade7 ASC, firstname ASC, quiza.id ASC LIMIT 0, 250;

      Show
      Altering the query such that the second left join called does not return so many results seems to get it. If you change the query above to the one below you get the same results, in a fraction of the time (query above reduced to 2 seconds. The additional line is on the second left join having it only include attempts for the quiz we are looking at as opposed to all quizzes. ' where qa7qa.questionusageid in (select uniqueid from mdl_quiz_attempts where quiz = '434' ) SELECT CONCAT(u.id, '#', COALESCE(quiza.attempt, 0)) AS uniqueid, quiza.uniqueid AS usageid, quiza.id AS attempt, u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.institution, u.department, u.email, quiza.sumgrades, quiza.timefinish, quiza.timestart, CASE WHEN quiza.timefinish = 0 THEN null WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart ELSE 0 END AS duration, COALESCE(( SELECT MAX(qqr.regraded) FROM mdl_quiz_overview_regrades qqr WHERE qqr.questionusageid = quiza.uniqueid ), -1) AS regraded, qa7.fraction * qa7.maxmark AS qsgrade7 FROM mdl_user u LEFT JOIN mdl_quiz_attempts quiza ON quiza.userid = u.id AND quiza.quiz = '434' LEFT JOIN ( SELECT qa7qa.id AS questionattemptid, qa7qa.questionusageid, qa7qa.slot, qa7qa.behaviour, qa7qa.questionid, qa7qa.variant, qa7qa.maxmark, qa7qa.minfraction, qa7qa.flagged, qa7qa.questionsummary, qa7qa.rightanswer, qa7qa.responsesummary, qa7qa.timemodified, qa7qas.id AS attemptstepid, qa7qas.sequencenumber, qa7qas.state, qa7qas.fraction, qa7qas.timecreated, qa7qas.userid FROM mdl_question_attempts qa7qa JOIN mdl_question_attempt_steps qa7qas ON qa7qas.id = ( SELECT MAX(id) FROM mdl_question_attempt_steps WHERE questionattemptid = qa7qa.id ) where qa7qa.questionusageid in (select uniqueid from mdl_quiz_attempts where quiz = '434' ) ) qa7 ON qa7.questionusageid = quiza.uniqueid AND qa7.slot = '7' WHERE quiza.id IS NOT NULL AND quiza.preview = 0 ORDER BY qsgrade7 ASC, firstname ASC, quiza.id ASC LIMIT 0, 250;
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      It seems the query for getting the data for 'mod/quiz/report.php?id=xxxx&mode=overview' page returns an overly large number of results.

      The query can take minutes to execute, included below.

      1. Query_time: 70.228014 Lock_time: 0.000520 Rows_sent: 87 Rows_examined: 48814592
        use moodle;
        SET timestamp=1320323743;
        SELECT
        CONCAT(u.id, '#', COALESCE(quiza.attempt, 0)) AS uniqueid,
        quiza.uniqueid AS usageid,
        quiza.id AS attempt,
        u.id AS userid,
        u.idnumber,
        u.firstname,
        u.lastname,
        u.picture,
        u.imagealt,
        u.institution,
        u.department,
        u.email,
        quiza.sumgrades,
        quiza.timefinish,
        quiza.timestart,
        CASE WHEN quiza.timefinish = 0 THEN null
        WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
        ELSE 0 END AS duration, COALESCE((
        SELECT MAX(qqr.regraded)
        FROM mdl_quiz_overview_regrades qqr
        WHERE qqr.questionusageid = quiza.uniqueid
        ), -1) AS regraded,
        qa7.fraction * qa7.maxmark AS qsgrade7
        FROM
        mdl_user u
        LEFT JOIN mdl_quiz_attempts quiza ON
        quiza.userid = u.id AND quiza.quiz = '434'
        LEFT JOIN (
        SELECT
        qa7qa.id AS questionattemptid,
        qa7qa.questionusageid,
        qa7qa.slot,
        qa7qa.behaviour,
        qa7qa.questionid,
        qa7qa.variant,
        qa7qa.maxmark,
        qa7qa.minfraction,
        qa7qa.flagged,
        qa7qa.questionsummary,
        qa7qa.rightanswer,
        qa7qa.responsesummary,
        qa7qa.timemodified,
        qa7qas.id AS attemptstepid,
        qa7qas.sequencenumber,
        qa7qas.state,
        qa7qas.fraction,
        qa7qas.timecreated,
        qa7qas.userid

      FROM mdl_question_attempts qa7qa
      JOIN mdl_question_attempt_steps qa7qas ON
      qa7qas.id = (
      SELECT MAX(id)
      FROM mdl_question_attempt_steps
      WHERE questionattemptid = qa7qa.id
      )
      ) qa7 ON qa7.questionusageid = quiza.uniqueid AND qa7.slot = '7'
      WHERE quiza.id IS NOT NULL AND quiza.preview = 0
      ORDER BY qsgrade7 ASC, firstname ASC, quiza.id ASC LIMIT 0, 250;

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                28/Nov/11