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

Add question to quiz query has wrong join on mdl_question_references causing performance issues

XMLWordPrintable

    • MOODLE_401_STABLE, MOODLE_402_STABLE
    • MOODLE_401_STABLE, MOODLE_402_STABLE
    • MDL-78622_401
    • Hide

      Testing that this change does not cause any regressions will be covered by unit tests.

      To test that this change acually fixes the performance problem requies a Moodle site with a large number of questions and quizzes already set up. So, we could just rely on santoshnagargoje's testing of my patch, reported below. Or, if you have access to such a site, you can test as follows:

      1. As teacher (or admin) create a Quiz in a test course.
      2. Go to the 'Questions' page in the secondary nav, so you can add questions.
      3. Choose Add -> A new question, to add a True False question. (Question type does not matter, but TF is fast to create.)
      4. Again, choose, Add -> A new question, to add a True False question.
      5. Verify that when you click the 'Save changse' button to finish creating the question, there is not a long delay. (Before this fix, with a large questoin bank and lots of quizzes, there was - probably good if you were able to reproduce that on your test site before trying to verify this fix.)
      Show
      Testing that this change does not cause any regressions will be covered by unit tests. To test that this change acually fixes the performance problem requies a Moodle site with a large number of questions and quizzes already set up. So, we could just rely on santoshnagargoje 's testing of my patch, reported below. Or, if you have access to such a site, you can test as follows: As teacher (or admin) create a Quiz in a test course. Go to the 'Questions' page in the secondary nav, so you can add questions. Choose Add -> A new question, to add a True False question. (Question type does not matter, but TF is fast to create.) Again, choose, Add -> A new question, to add a True False question. Verify that when you click the 'Save changse' button to finish creating the question, there is not a long delay. (Before this fix, with a large questoin bank and lots of quizzes, there was - probably good if you were able to reproduce that on your test site before trying to verify this fix.)

      During our large staging site Load and Performance testing found that if you add following 2 indexes on mdl_question_references table 
      1. composite index on (component, questionarea)
      2. single index on (itemid) 
      It would improve the quiz's "Add question" feature by adding single question and from question bank a lot if you are running a large moodle site.

      SELECT qbe.id
                    FROM {quiz_slots} slot
                    JOIN {question_references} qr ON qr.itemid = slot.id
                    JOIN {question_bank_entries} qbe ON qbe.id = qr.questionbankentryid
                   WHERE slot.quizid = ?
                     AND qr.component = ?
                     AND qr.questionarea = ?

      we tested that this query takes 50 seconds to search records from 23 million records with current indexes but after adding above suggested indexes it took only 0 seconds.

       

      So I would strongly recommend this as default to create index with these fields. Please find the patches in the attachment.

            timhunt Tim Hunt
            santoshnagargoje Santosh Nagargoje
            Santosh Nagargoje Santosh Nagargoje
            Andrew Lyons Andrew Lyons
            CiBoT CiBoT
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 51 minutes
                51m

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