search

SVIeSolutions / moodle-mod_via

1 stars 2 forks source link

Synching participants doesn't scale on big databases #4

Open dkns opened 8 years ago

dkns commented 8 years ago

This query:

// Add users from group synch.
$newgroupmemberssql = ' FROM {via} v
                        LEFT JOIN {groupings_groups} gg ON v.groupingid = gg.groupingid
                        LEFT JOIN {groups_members} gm ON gm.groupid = gg.groupid
                        LEFT JOIN {via_participants} vp ON vp.activityid = v.id AND vp.userid = gm.userid ';
$newgroupmemberswhere = ' WHERE v.groupingid != 0 AND vp.id is null ';
$newgroupmembers = $DB->get_recordset_sql('select distinct v.id as activityid, v.course, v.noparticipants, gm.userid
                                        '.$newgroupmemberssql.' '.$newgroupmemberswhere);

doesn't finish when executed on big tables. In our case via_participants has around ~630000 records and groups_members has around ~130000 records. If LIMIT 1 is used the query finish instantaneously however increasing LIMIT increases execution time.

Let me know if you need any more info.

SVIeSolutions commented 8 years ago

Thanks for the comment, I will look into it!!! I have many improvements that I need to do!!!

dkns commented 8 years ago

Thanks! Let me know if you need any help with it.

SVIeSolutions commented 8 years ago

We have added a new version : 2016042006. We hope that it will fix your issue. Our moodles are smaller and we have never had this issue. Please let us know if it fixes this bug. Thank you for your patience...