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

Slow quiz statistics query

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 4.1.5
    • Questions, Quiz
    • None
    • MOODLE_401_STABLE

      We were not experiencing the deadlock issue in MDL-78580, but the question bank and quiz statistics pages were taking upwards of 40 seconds to load, if they did not timeout. We were also seeing the timeout on the quiz_statistics cron task, so it never finished. We used the cron script for debugging and found a delete query that was taking 2.5-4 seconds per question during the task in question/classes/statistics/responses/analysis_for_question.php:211:

      $DB->delete_records_select('question_response_count',
                  'analysisid IN (
                      SELECT id
                        FROM {question_response_analysis}
                       WHERE hashcode= ? AND whichtries = ? AND questionid = ?
                      )', [$qubaids->get_hash_code(), $whichtries, $questionid]);

      We replaced this query with:

      $DB->execute("
                  DELETE qrc
                  FROM mdl_question_response_count qrc
                  JOIN mdl_question_response_analysis qra
                  ON qrc.analysisid = qra.id
                  WHERE qra.hashcode = ?
                  AND qra.whichtries = ?
                  AND qra.questionid = ?",
                  [$qubaids->get_hash_code(), $whichtries, $questionid]);

      and now the query runs in 0.00038 seconds, we were able to get the cron task to complete, and the pages load significantly faster now.

      We also added indexes to a few of the impacted tables:

      create index mdl_question_response_analysis_test_idx on mdl_question_response_analysis (hashcode, whichtries, questionid);
      create index mdl_question_response_count_test_idx on mdl_question_response_count (analysisid);

      I realize we need to adapt the query for other supported DB engines, and would ask for the community's help in developing that, as we run a MySQL instance. Please advise of preferred next steps. Thank you!

            Unassigned Unassigned
            meltonml Michelle Melton
            Votes:
            18 Vote for this issue
            Watchers:
            22 Start watching this issue

              Created:
              Updated:

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