Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-7702

Performance issue with query fetching from log table during checklist grade export to Excel



    • Bug
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • 3.5.4
    • None
    • Package: Checklist
    • None
    • 2017110900


      We've found that the following query being constructed in gradeexport_checklist has a severe performance impact on a client site when checklist data is exported to Excel format from within the course, running upwards of 40 seconds per-query and eventually causing the database sessions to time out:

      /moodle-gradeexport_checklist.git$ sed -n '278,280p' export.php 
                          $select = "userid = ? AND courseid = ? AND target = 'course' AND action = 'viewed'";
                          $params = array($user->id, $course->id);
                          $events = $reader->get_events_select($select, $params, 'timecreated ASC', 0, 1);

      Another member of our team found that this seems to be caused by the combination of ORDER BY and LIMIT clauses on the log table, since removing either of them reduces the query time significantly down to milliseconds. However I do see that as-written those are both necessary to fetch the single record of interest, since depending on RDBMS the order of results is not guaranteed (i.e. just because log records are inserted in-order does not guarantee that they'll be returned in any given order.)

      So it appears that this query may need to be rewritten to be more efficient, possibly constructing a manual query to call get_records_sql instead using some other method to fetch the single record without sorting the entire resultset. Something using a subselect like "WHERE id = (select max(id) from ...)" might work, although I've not tested this and is just an idea of one way this might be accomplished.




            davosmith Davo Smith
            lreynolds Logan Reynolds
            1 Vote for this issue
            5 Start watching this issue