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

Quiz upgrade SQL performance improvement

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.2.1, 2.3
    • Fix Version/s: 2.1.5, 2.2.2
    • Component/s: Quiz
    • Labels:
    • Database:
      MySQL
    • Testing Instructions:
      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.)
    • Affected Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull Master Branch:
      MDL-31495-master

      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.

        Gliffy Diagrams

          Attachments

            Activity

            maherne Michael Aherne created issue -
            timhunt Tim Hunt made changes -
            Field Original Value New Value
            Fix Version/s STABLE backlog [ 10463 ]
            Labels triaged
            maherne Michael Aherne made changes -
            maherne Michael Aherne made changes -
            Attachment MDL-31495-test.php [ 26730 ]
            timhunt Tim Hunt made changes -
            Status Open [ 1 ] Waiting for integration review [ 10010 ]
            Fix Version/s 2.1.5 [ 11553 ]
            Fix Version/s 2.2.2 [ 11552 ]
            Fix Version/s STABLE backlog [ 10463 ]
            Peer reviewer timhunt
            Testing Instructions 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.)
            timhunt Tim Hunt made changes -
            Assignee Tim Hunt [ timhunt ] Michael Aherne [ maherne ]
            stronk7 Eloy Lafuente (stronk7) made changes -
            Currently in integration Yes [ 10041 ]
            nebgor Aparup Banerjee made changes -
            Status Waiting for integration review [ 10010 ] Integration review in progress [ 10004 ]
            Integrator nebgor
            nebgor Aparup Banerjee made changes -
            Status Integration review in progress [ 10004 ] Waiting for testing [ 10005 ]
            Affects Version/s 2.3 [ 10657 ]
            Fix Version/s 2.3 [ 10657 ]
            Fix Version/s 2.2.2 [ 11552 ]
            Fix Version/s 2.1.5 [ 11553 ]
            nebgor Aparup Banerjee made changes -
            Testing Instructions 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.)
            Tester: please use attached script to test this patch. (see comments)

            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.)
            nebgor Aparup Banerjee made changes -
            Fix Version/s 2.1.5 [ 11553 ]
            Fix Version/s 2.2.2 [ 11552 ]
            Fix Version/s 2.3 [ 10657 ]
            Testing Instructions Tester: please use attached script to test this patch. (see comments)

            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.)
            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.)
            maherne Michael Aherne made changes -
            Comment [ To put the performance gain in perspective, I was seeing a decrease in time of about 15-20 seconds per quiz (running it on MySQL with a database of slightly over 2 million question sessions and over 900,000 attempts). Granted, this was on a not particularly good test box, but if you multiply that by 5,900-odd quizzes it's a time saving of over 20 hours!


            ]
            salvetore Michael de Raadt made changes -
            Tester rajeshtaneja
            rajeshtaneja Rajesh Taneja made changes -
            Status Waiting for testing [ 10005 ] Testing in progress [ 10011 ]
            rajeshtaneja Rajesh Taneja made changes -
            Status Testing in progress [ 10011 ] Tested [ 10006 ]
            stronk7 Eloy Lafuente (stronk7) made changes -
            Status Tested [ 10006 ] Closed [ 6 ]
            Resolution Fixed [ 1 ]
            Currently in integration Yes [ 10041 ]
            stronk7 Eloy Lafuente (stronk7) made changes -
            Integration date 17/Feb/12

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  12/Mar/12