Minor This problem happens on a big dataset, in a course with more than 2000 students and a quiz with 2000 attempts.
Then when we execute the task adhoc quiz_statistics\task\recalculate for this quiz, we got an exception for out of memory : PHP Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 16384 bytes)
I checked the code and the error comes from the function load_attempts_at_question in file question/engine/datalib.php. If I execute the query directly from the database (with phpmyadmin or dbeaver) it works fine but it can't be done when executing with $DB->get_recordset_sql()
Is there anyway to improve this query? I checked the returned results and there are many duplicated results.
SELECT quba.contextid,quba.preferredbehaviour,qa.id AS questionattemptid,qa.questionusageid,qa.slot,
|
qa.behaviour,qa.questionid,qa.variant,qa.maxmark,qa.minfraction,qa.maxfraction,qa.flagged,
|
qa.questionsummary,qa.rightanswer,qa.responsesummary,qa.timemodified,qas.id AS attemptstepid,
|
qas.sequencenumber,qas.state,qas.fraction,qas.timecreated,qas.userid,qasd.value
|
FROM mdl_quiz_attempts quiza
|
JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
|
JOIN mdl_question_usages quba ON quba.id = qa.questionusageid
|
LEFT JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id
|
LEFT JOIN mdl_question_attempt_step_data qasd ON qasd.attemptstepid = qas.id
|
WHERE quiza.quiz = :quizid AND quiza.preview = 0 AND quiza.state = 'finished' AND (quiza.state = 'finished' AND NOT EXISTS ( |
SELECT 1 FROM mdl_quiz_attempts qa2 |
WHERE qa2.quiz = quiza.quiz AND qa2.userid = quiza.userid AND qa2.state = 'finished' AND ( |
COALESCE(qa2.sumgrades, 0) > COALESCE(quiza.sumgrades, 0) OR |
(COALESCE(qa2.sumgrades, 0) = COALESCE(quiza.sumgrades, 0) AND qa2.attempt < quiza.attempt) |
))) AND quiza.sumgrades IS NOT NULL AND qa.questionid = :questionid
|
ORDER BY quba.id,qa.id,qas.sequencenumber;
|
- will be (partly) resolved by
-
MDL-80104 Create an admin tool to migrate bad base64 inline data into file api
-
- Open
-