Slow query (assign)

Slow query (assign)

by Iban Cardona -
Number of replies: 4

Hello,

We have a Moodle with version: 3.3.9 (Build: 20181112).

In 1 assign of 1 course (in other big courses no problem) we have an issue: We need between three and five minutes to load the page to grade. We searched in the database and we saw that this query in /mod/assign/gradingtable.php takes 67 seconds:


SELECT
                u.id,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email, u.id as userid, s.status as status, s.id as submissionid, s.timecreated as firstsubmission, CASE WHEN status <> 'new' THEN s.timemodified ELSE NULL END as timesubmitted, s.attemptnumber as attemptnumber, g.id as gradeid, g.grade as grade, g.timemodified as timemarked, g.timecreated as firstmarked, uf.mailed as mailed, uf.locked as locked, uf.extensionduedate as extensionduedate, uf.workflowstate as workflowstate, uf.allocatedmarker as allocatedmarker, priority.priority, effective.allowsubmissionsfromdate, effective.duedate, effective.cutoffdate
                FROM mdl_user u
                         LEFT JOIN mdl_assign_submission s
                                ON u.id = s.userid
                               AND s.assignment = '86494'
                               AND s.latest = 1
                         LEFT JOIN mdl_assign_grades g
                                ON u.id = g.userid
                               AND g.assignment = '86494' AND g.attemptnumber = s.attemptnumber LEFT JOIN mdl_assign_user_flags uf
                         ON u.id = uf.userid
                        AND uf.assignment = '86494'  LEFT JOIN (
               SELECT merged.userid, min(merged.priority) priority FROM (
                  ( SELECT u.id as userid, 9999999 AS priority
                      FROM mdl_user u
                  )
                  UNION
                  ( SELECT uo.userid, 0 AS priority
                      FROM mdl_assign_overrides uo
                     WHERE uo.assignid = '86494'
                  )
                  UNION
                  ( SELECT gm.userid, go.sortorder AS priority
                      FROM mdl_assign_overrides go
                      JOIN mdl_groups g ON g.id = go.groupid
                      JOIN mdl_groups_members gm ON gm.groupid = g.id
                     WHERE go.assignid = '86494'
                  )
                ) merged
                GROUP BY merged.userid
              ) priority ON priority.userid = u.id

            JOIN (
              (SELECT 9999999 AS priority,
                      u.id AS userid,
                      a.allowsubmissionsfromdate,
                      a.duedate,
                      a.cutoffdate
                 FROM mdl_user u
                 JOIN mdl_assign a ON a.id = '86494'
              )
              UNION
              (SELECT 0 AS priority,
                      uo.userid,
                      uo.allowsubmissionsfromdate,
                      uo.duedate,
                      uo.cutoffdate
                 FROM mdl_assign_overrides uo
                WHERE uo.assignid = '86494'
              )
              UNION
              (SELECT go.sortorder AS priority,
                      gm.userid,
                      go.allowsubmissionsfromdate,
                      go.duedate,
                      go.cutoffdate
                 FROM mdl_assign_overrides go
                 JOIN mdl_groups g ON g.id = go.groupid
                 JOIN mdl_groups_members gm ON gm.groupid = g.id
                WHERE go.assignid = '86494'
              )

            ) effective ON effective.priority = priority.priority AND effective.userid = priority.userid
                WHERE u.id IN ('228722','228149','168189','229367','229805','227938','5560','219930','228045','229821','228560','227835','229792','227908','228132','227976','228654','230221','228626','228181','228104','229983','106161','220144','229750','227979','227971','228154','4323','228101','227862','229995','5685','228094','227804','228866','228241','228224','227850','228240','228141','228480','65793','227788','227770','71086','211997','33013','227919','229949','81779','227876','228073','227935','220079','229753','229946','229217','229762','228262','109127','229770','228552','211294','228477','229580','228629','218857','229961','60687','75253','229381','154188','228179','229811','227917','229473','227913','228013','227765','229664','229819','152487','227852','228219','227974','230102','8830','229985','229548','199116','228105','47495','122094','228628','228037','227992','220142','218611','220062','229356','227800','43005','228070','229578','228623','135190','229945','230418','228147','230276','227841','219980','219877','227827','230287','220226','228129','228191','229984','228030','220116','228049','227758','228510','228130','23636','209896','228716','202966','227901','24732','230160','227810','228491','229333','218788','227875','229767','229329','228064','227819','227984','106349','229871','228018','229137','228016','227829','59986','82700','93097','214381','230065','228437','228170','229954','229751','209827','227793','228284','175613','229565','220324','228620','220321','227898','227998','70540','228291','228015','228211','55547','188990','3541','101463','228023','5140','227803','227806','227774','230273','219944','229315','227867','38462','227920','227780','228378','228356','167235','45250','228038','227890','220298','228289','228271','227818','230244','227789','227905','230285','227811','229363','220193','228556','220194','228452','123138','228339','230320','227978','228183','229282','228186','229826','227844','230072','229160','171011','228052','228283','79734','227836','220190','229377','230019','70425','218693','210','229078','219894','229739','230411','227851','227792','227918','227999','228636','229795','229964','228651','229970','229755','48236','227883','227878','227968','230163','228278','228136','228270','219947','228251','228225','229667','75873','228358','227906','227896','228469','228618','228479','219971','228158','227895','228644','227760','230403','199199','227993','75929','202913','227768','227988','206854','229033','229812','230374','227797','229994','218569','228081','228142','219948','228193','229102','218919','105539','229982','22851','113922','172433','228647','35567','228113','229957','228022','229800','228006','228322','217340','229009','91124','230256','227945','126454','227785','229713','227975','229741','230121','228279','227904','227830','227926','227882','228084','227763','229103','227858','78036','228244','228011','229955','228562','228239','227960','228027','230414','227786','228176','113902','220333','133113','228206','227808','227828','211957','227942','617','228168','228053','229584','230367','230013','227807','228097','97809','229969','229405','209841','227772','104596','228650','95558','219968','228624','227842','228468','34032','229748','227766','227934','224248','228190','228392','230018','11027','223227','228080','220302','228231','228242','227839','227985','227864','228140','122443','219908','227779','229988','228109','228209','227799','228245','228210','228042','228351','212281','228273','199941','227775','228625','220309','228103','82187','228492','220105','228662','227801','227816','57438','28680','229075','155852','223685','227891','228001','228790','228653','227795','172552','229085','220172','227838','228302','5541','9028','219912','228048','229824','228139','227871','227813','227986','230083','220177','227915','227771','229786','220174','229680','174966','230286','214625','228487','229122','228614','229368','227790','218541','227840','229796','228570')
                ORDER BY userid ASC LIMIT 0, 10;

Any suggestion?

Thank you so much!

Average of ratings: -
In reply to Iban Cardona

Re: Slow query (assign)

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Hi Iban, just a suggestion, usually when faced with a complicated slow query like that, the best approach to track down why it's slow is to (intelligently) remove chunks of the code until it stops being slow, so that you can narrow down the slow part. You could try that? Maybe get it down to the minimum possible query that is still slow...

The query looks hideous but maybe it has been optimised already if it's in the assign tool, I guess a lot of people use it... Anyway I'm not knowledgeable about assign and I can't really tell anything about the query just from looking. That said, the sub-query that is being LEFT JOINED which appears to include every user in the database (supposing the database decided to materialise it) seems like it could be bad.

Might be worth mentioning which database you're using, so that you can get lots of helpful responses like 'Use Postgres' (unless you already do in which case the helpful response is something else). More seriously there are some patterns that can be hideously slow on one database and not particularly slow on another one.
Average of ratings: -
In reply to Iban Cardona

Re: Slow query (assign)

by Michael Hawkins -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Testers

Hi Iban,

It looks like this has been raised in Tracker issue MDL-65797 (Performance improvements for user / group overrides for mod assign), which has been addressed in Moodle 3.8. It may be worth looking at whether upgrading is a suitable option (and if not, perhaps it is worth looking at the patch that was applied in that fix).

Average of ratings: -
In reply to Michael Hawkins

Re: Slow query (assign)

by Iban Cardona -
Hello,

Thank you so much for the answers. We will continue to investigate whether we find the solution, either updating the site or changing the query
Average of ratings: -
In reply to Iban Cardona

Re: Slow query (assign)

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi,

I've tested the query in your environment and it's not slow.
We use Moodle 3.7.1+ debian 9 apache. I've seen that you've 445 students in that query it's a lot but not unusual.
We use perconna on a mysql database. it took some seconds.

When you want to speed up a query a good way it's to reduce the scan on table for instance here can you try to replace the second line
FROM mdl_user u
by
FROM (SELECT * FROM mdl_user WHERE id IN ('228722','228149','168189','229367','229805','227938','5560','219930','228045','229821','228560','227835','229792','227908','228132','227976','228654','230221','228626','228181','228104','229983','106161','220144','229750','227979','227971','228154','4323','228101','227862','229995','5685','228094','227804','228866','228241','228224','227850','228240','228141','228480','65793','227788','227770','71086','211997','33013','227919','229949','81779','227876','228073','227935','220079','229753','229946','229217','229762','228262','109127','229770','228552','211294','228477','229580','228629','218857','229961','60687','75253','229381','154188','228179','229811','227917','229473','227913','228013','227765','229664','229819','152487','227852','228219','227974','230102','8830','229985','229548','199116','228105','47495','122094','228628','228037','227992','220142','218611','220062','229356','227800','43005','228070','229578','228623','135190','229945','230418','228147','230276','227841','219980','219877','227827','230287','220226','228129','228191','229984','228030','220116','228049','227758','228510','228130','23636','209896','228716','202966','227901','24732','230160','227810','228491','229333','218788','227875','229767','229329','228064','227819','227984','106349','229871','228018','229137','228016','227829','59986','82700','93097','214381','230065','228437','228170','229954','229751','209827','227793','228284','175613','229565','220324','228620','220321','227898','227998','70540','228291','228015','228211','55547','188990','3541','101463','228023','5140','227803','227806','227774','230273','219944','229315','227867','38462','227920','227780','228378','228356','167235','45250','228038','227890','220298','228289','228271','227818','230244','227789','227905','230285','227811','229363','220193','228556','220194','228452','123138','228339','230320','227978','228183','229282','228186','229826','227844','230072','229160','171011','228052','228283','79734','227836','220190','229377','230019','70425','218693','210','229078','219894','229739','230411','227851','227792','227918','227999','228636','229795','229964','228651','229970','229755','48236','227883','227878','227968','230163','228278','228136','228270','219947','228251','228225','229667','75873','228358','227906','227896','228469','228618','228479','219971','228158','227895','228644','227760','230403','199199','227993','75929','202913','227768','227988','206854','229033','229812','230374','227797','229994','218569','228081','228142','219948','228193','229102','218919','105539','229982','22851','113922','172433','228647','35567','228113','229957','228022','229800','228006','228322','217340','229009','91124','230256','227945','126454','227785','229713','227975','229741','230121','228279','227904','227830','227926','227882','228084','227763','229103','227858','78036','228244','228011','229955','228562','228239','227960','228027','230414','227786','228176','113902','220333','133113','228206','227808','227828','211957','227942','617','228168','228053','229584','230367','230013','227807','228097','97809','229969','229405','209841','227772','104596','228650','95558','219968','228624','227842','228468','34032','229748','227766','227934','224248','228190','228392','230018','11027','223227','228080','220302','228231','228242','227839','227985','227864','228140','122443','219908','227779','229988','228109','228209','227799','228245','228210','228042','228351','212281','228273','199941','227775','228625','220309','228103','82187','228492','220105','228662','227801','227816','57438','28680','229075','155852','223685','227891','228001','228790','228653','227795','172552','229085','220172','227838','228302','5541','9028','219912','228048','229824','228139','227871','227813','227986','230083','220177','227915','227771','229786','220174','229680','174966','230286','214625','228487','229122','228614','229368','227790','218541','227840','229796','228570')) u

I hope it's help.

Dominique.
Average of ratings: -