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

Quiz upgrade SQL performance improvement

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 2.2.1, 2.3
    • 2.1.5, 2.2.2
    • Quiz
    • MySQL
    • MOODLE_22_STABLE, MOODLE_23_STABLE
    • MOODLE_21_STABLE, MOODLE_22_STABLE
    • MDL-31495-master
    • Hide

      Tester: please use attached script to test this patch. (see comments)
      Please test in 2.1.x , 2.2.x (and if possible test in master too)

      TO INTEGRATORS:

      please cherry-pick to all branches 2.1+. (I'm not sure if this is really needed on master, but if the code is there, the fix should be applied.)

      Show
      Tester: please use attached script to test this patch. (see comments) Please test in 2.1.x , 2.2.x (and if possible test in master too) TO INTEGRATORS: please cherry-pick to all branches 2.1+. (I'm not sure if this is really needed on master, but if the code is there, the fix should be applied.)

    Description

      In the developer forum (at http://moodle.org/mod/forum/discuss.php?d=195237) I suggested a rewrite of one of the SQL queries involved in the upgrade to the new quiz engine:

      Replace the following code (from question/engine/upgrade/upgradelib.php):

      $questionsstatesrs = $DB->get_recordset_sql("
      SELECT *
      FROM

      {question_states}
      WHERE attempt IN (
      SELECT uniqueid FROM {quiz_attempts} WHERE $where)
      ORDER BY attempt, question, seq_number, id
      ", $params);

      with this

      $questionsstatesrs = $DB->get_recordset_sql("
      SELECT DISTINCT s.*
      FROM {question_states}

      s
      JOIN

      {quiz_attempts}

      ON (s.attempt = uniqueid)
      WHERE $where
      ORDER BY s.attempt, question, seq_number, s.id
      ", $params);

      It seems to be equivalent and is faster.

      Attachments

        Activity

          People

            maherne Michael Aherne
            maherne Michael Aherne
            Tim Hunt Tim Hunt
            Aparup Banerjee Aparup Banerjee
            Rajesh Taneja Rajesh Taneja
            Tim Hunt, Ilya Tregubov, Kevin Percy, Mathew May, Mihail Geshoski, Shamim Rezaie
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              12/Mar/12