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

Quiz upgrade SQL performance improvement

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Fixed
    • Icon: Minor Minor
    • 2.1.5, 2.2.2
    • 2.2.1, 2.3
    • 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.)

      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.

            maherne Michael Aherne
            maherne Michael Aherne
            Tim Hunt Tim Hunt
            Aparup Banerjee Aparup Banerjee
            Rajesh Taneja Rajesh Taneja
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.