The organization I work for is in the early stages of setting up a Moodle instance. We're using version 1.7.0. We started using the external database enrolment plugin. When we ran our test cases, we saw that our database server was getting queried 38 times (once for each role we have!)
We're worried about performance. I realize 38 queries is not an extreme task for a server, but we will have thousands of users potentially logging in at the same time. Taking scale into it, it does seem like a big deal.
I looked at the source for the setup_enrolments function at /enrol/database/enrol.php, which is where the queries are taking place. The logic of this function doesn't make sense to me. Rather, I understand how it works, but I don't understand why it was written this way. I did a pseudocode outline of the function:
$roles = get_roles();
foreach($roles as $role) {
if ($rs = $enroldb->Execute("SELECT course FROM table WHERE id and role matches") {
do_enrolment_stuff();
}
}
It gets a list of all possible roles, does a query for each role, and for each query, it updates any course roles for that user for that particular role. This seems excessive to me. Wouldn't it work if the entire recordset for the user were retrieved in a single query, and then each course that appeared in the set were given the appropriate role for that user? Like this:
$rs = "SELECT * FROM table WHERE id matches";
foreach row {
if (user doesn't already have this role for this course) {
grant row->role to this user, for row->course;
}
}
It could be that we're overlooking something here. Therefore we decided to ask you, the Moodle community, if this sort of change:
a) would work
b) be beneficial to us
Thanks,
Keith