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

Essay question type: potentially inefficient DB query in restore code

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.4.6, 3.5.3, 3.6.1
    • Fix Version/s: 3.5.5, 3.6.3
    • Component/s: Questions
    • Labels:

      Description

      MDL-51412 addressed the issue by adding index to qtype for mdl_question which optimises the query used during restore. However, this code may need optimisation
      line 97 of question/type/essay/backup/moodle2/restore_qtype_essay_plugin.class.php

      // code placeholder
          protected function after_execute_question() {
              global $DB;
       
       
              $essayswithoutoptions = $DB->get_records_sql("
                          SELECT *
                            FROM {question} q
                           WHERE q.qtype = ?
                             AND NOT EXISTS (
                              SELECT 1
                                FROM {qtype_essay_options}
                               WHERE questionid = q.id
                           )
                      ", array('essay'));
      

      For very large sites, this takes a lot.

      // code placeholder
      SELECT *
      FROM mdl_question q
      WHERE q.qtype = 'essay'
      AND NOT EXISTS (
      SELECT 1
      FROM mdl_qtype_essay_options
      WHERE questionid = q.id
      );
                                                QUERY PLAN                                          
      ----------------------------------------------------------------------------------------------
       Hash Anti Join  (cost=83414.77..1247847.52 rows=1061038 width=391)
         Hash Cond: (q.id = mdl_qtype_essay_options.questionid)
         ->  Bitmap Heap Scan on mdl_question q  (cost=25571.59..1057620.12 rows=1128132 width=391)
               Recheck Cond: ((qtype)::text = 'essay'::text)
               ->  Bitmap Index Scan on mdl_ques_qty_ix  (cost=0.00..25289.55 rows=1128132 width=0)
                     Index Cond: ((qtype)::text = 'essay'::text)
         ->  Hash  (cost=40369.86..40369.86 rows=1064986 width=8)
               ->  Seq Scan on mdl_qtype_essay_options  (cost=0.00..40369.86 rows=1064986 width=8)
      (8 rows)
      

      This looks to set defaults on broken question records in case it has restored any. However, it looks to end up looking at"all" questions to do that. Rather than just inspecting the restored ones.

        Attachments

          Activity

            People

            Assignee:
            timhunt Tim Hunt
            Reporter:
            jebarvia Joshua Ebarvia
            Peer reviewer:
            Peter Dias Peter Dias
            Integrator:
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Tester:
            CiBoT CiBoT
            Participants:
            Component watchers:
            Tim Hunt, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              11/Mar/19