Gradebook takes long time to display

Gradebook takes long time to display

by António Vilela -
Number of replies: 10
Picture of Plugin developers Picture of Translators

The student gradebook is taking more than 30 seconds to display. Does anybody have the same problem?

Moodle is running in very fast a dedicated server so it is not due to the server performance.

Analysing MySQL processes it seems that Moodle gradebook queryes the table mdl_scorm_scoes_track once for each student and SCO. Here is a sample taken every 4 seconds:

SELECT *
FROM mdl_scorm_scoes_track
WHERE userid =133
AND scoid =87
ORDER BY element ASC 

SELECT *
FROM mdl_scorm_scoes_track
WHERE userid =104
AND scoid =428
ORDER BY element ASC 

SELECT *
FROM mdl_scorm_scoes_track
WHERE userid =91
AND scoid =535
ORDER BY element ASC

SELECT *
FROM mdl_scorm_scoes_track
WHERE userid =118
AND scoid =72
ORDER BY element ASC 

Having a course with 200 students and 10 SCORM packages with 50 SCO's each, this means 100,000 queries.

Why does the GradeBook analyse every student scoes_track to show a single student grade? Moreover, my SCOES are using SUMgrade so this queries seem irrelevant.

Can anybody give me a clue of what's happening?

In reply to António Vilela

Re: Gradebook takes long time to display

by Jeff Graham -
Each module has its own grade function. The gradebook calls this function for each module and each instance. The problem that you are experiencing seems to be an issue with the SCORM module and its grades function.

I'm not familiar enough with the SCORM module to comment on what is going on past this point.
In reply to Jeff Graham

Re: Gradebook takes long time to display

by António Vilela -
Picture of Plugin developers Picture of Translators

Thanks Jeff

Your answer really has shown me the way.

I found a function called scorm_grades in mod/scorm/lib.php which is used every time a student, teacher or admin acesses the gradebook. This function returns, for a specified scorm package, an array containing the score for each student.

The problem is that every SCO/USER is analized, either the evaluation method is SCOs situation, SUM , Average or High. I changed the function to query only the SCO's with 'cmi.score.raw' or 'cmi.core.score.raw' records when the evaluation method is different from SCO's situation, which is the case of my SCORM packages.

The Gradebook now displays in 5 to 10 seconds, when it was taking more than 1 minute to display.

If anybody has the same problem I can share the code changes.

In reply to Jeff Graham

Re: Gradebook takes long time to display

by António Vilela -
Picture of Plugin developers Picture of Translators

Hello again

I decided to improve a little bit more my gradebook speed.

I changed the entire function scorm_grades.

Now it works with a single query to the database, using the MySQL GROUP BY (Aggregate) Functions SUM, AVG, MAX and COUNT.

The gradebook takes 2-3 seconds to display.

Again I am available to share the code changes.

Thanks for the help

In reply to António Vilela

Re: Gradebook takes long time to display

by Rob Barreca -
I feel like this brings up a bigger issue about the gradebook that I've run into. I'm using a customized version of the CDC/gradebookplus and I have a teacher with around 200 categorized "assignments".

She's getting a fatal error that the execution is taking longer that 30 seconds on the main View Grades page and Edit Grades. I've been brainstorming some ways to get arounds this...

1. One way would be to limit the number of assignments shown on any page without forcing teachers to use categories. This is not a good approach as people want to see ALL their grades very quickly, but maybe some sort of pager that would let you scroll through chunks of 20 assignments horizontally?

2. Another way would be to limit the number of students on each page by using a vertical pager.

I guess it seems that it is taking a long time to run all these queries (even longer on the View Grades page when showing grades for each category). I think that in 1.6 there will be centralized grades that will push to the grades table, so I'm sure this will help.

I look forward to any input anyone may have on how to improve this performance in 1.5.3+.
In reply to Rob Barreca

Re: Gradebook takes long time to display

by Rob Barreca -
In my slow mysql query logs I have a bunch of these:

# Query_time: 23  Lock_time: 0  Rows_sent: 512880  Rows_examined: 1312118
SELECT e.id, e.userid, gi.cminstance, gi.modid, c.name as catname, mm.name as modname
                                    FROM mdl_grade_exceptions e,
                                        mdl_grade_item gi,
                                        mdl_grade_category c,
                                        mdl_course_modules cm,
                                        mdl_modules mm
                                    WHERE e.courseid=57
                                        AND gi.id = e.grade_itemid
                                        AND c.id = gi.category
                                        AND cm.course=c.courseid
                                        AND cm.module=mm.id
                                        AND gi.modid=mm.id;

As this teacher has maybe 200 assignments and a decent amount of exceptions...but 512880 is a bit overkill! I'm trying to debug this now, but could use some insight into how to optimize this query as this is a big oversight in the gradebook.
In reply to Rob Barreca

Re: Gradebook takes long time to display

by Rob Barreca -
I've changed the query as stated below -- taking out the reference to the course_modules table as it seemed unnecessary to figure out the module's name. Please would a Moodle "expert" (Michael Penney I'm looking your way wink) double-check this, I'm pretty sure it's correct now as it pulls the correct number of rows (4000 instead of 500,000). And it takes about 0.05 seconds as opposed to 33.5 seconds.

This should help speed that gradebook up!

function grade_get_exceptions($course) {
    global $CFG;
    $sql = "SELECT e.id, e.userid, gi.cminstance, gi.modid, c.name as catname, mm.name as modname
            FROM {$CFG->prefix}grade_exceptions e
            LEFT JOIN {$CFG->prefix}grade_item gi ON (gi.id = e.grade_itemid)
            LEFT JOIN {$CFG->prefix}grade_category c ON (c.id = gi.category)
            LEFT JOIN {$CFG->prefix}modules mm ON (mm.id = gi.modid)
            WHERE e.courseid=$course";
 
    $temp = get_records_sql($sql);
    return $temp;
}
In reply to Rob Barreca

Re: Gradebook takes long time to display

by Jeff Graham -
Rob,

That looks good, I didn't use the join conditions because of moodle's previous target version for MySQL not supporting the join on conditions. With moodle 1.6 this shouldn't be an issue and your fix should be good. I'll push that in when I get a chance to test unless somebody else beats me to it; the other custom sql should be updated to use join conditions where possible as well.

regardless of the above there should have been a lot more restrictions in the where clause looking at it now. smile

regards,
Jeff
In reply to Jeff Graham

Re: Gradebook takes long time to display

by Rob Barreca -
I just realized it is better to use the commas (which is really an INNER JOIN) so a better query would be the following.

$sql = "SELECT e.id, e.userid, gi.cminstance, gi.modid, c.name as catname, mm.name as modname
            FROM {$CFG->prefix}grade_exceptions e,
                {$CFG->prefix}grade_item gi,
                {$CFG->prefix}grade_category c,
                {$CFG->prefix}modules mm
            WHERE e.courseid=$course
                AND gi.id = e.grade_itemid
                AND c.id = gi.category
                AND gi.modid=mm.id";
In reply to Rob Barreca

Re: Gradebook takes long time to display

by António Vilela -
Picture of Plugin developers Picture of Translators

Rob

It seems a quite complex situation you are dealing with. I never used exceptions so I never examined the gradebook in that perspective.

But, if your query is performing well, you should report your problem as a moodle bug because the original query was generating an unnecessary one to many (records) relationship which cause the unwanted delay.

e->gi->c->cm->mm->(back to) gi