Best practice forum (Archived)

Large Dynamic Cohorts

Hi,

Creating dynamic cohorts with a very large (tens of thousands) user list will fail because the SQL being generated to insert into cohort_members is too big a query for our MySQL servers.

In line 311 of cohorts/lib.php you'll find the code where this query is being generated.

The obvious solution would be to do this insert in batches, but I don't really want to be modifying core Totara code and we don't know what else this would break.

Is there a better solution or a known fix for this? Will using dynamic cohorts of this size cause us any other problems elsewhere?

Permalink | Reply

Hi Haroon,

Another approach would be to use the following syntax:

INSERT INTO table (field1, field2, field3) SELECT field1,field2,field3 FROM ...

which should be cross-db compatible, but the way dynamic cohorts are structured I think that would be difficult in this case so doing the insert in batches is probably the way to go.

Dynamic cohorts in 1.1 are primarily used by program management I think you would have some performance issues doing the assignments on very large cohorts. The main issues are that course enrolments and sending emails in bulk is pretty slow.

There has been some work to make a faster bulk enrol function for another large client but the solution isn't currently cross-database compatible (postgres only).

Simon

Permalink | Show parent | Reply

Is this work with the large client likely to be included in core Totara or otherwise made available? Any timeframe for this?

If we were to change the code to do these inserts in batches and submitted a patch, would this be likely to be included in Totara core?

Permalink | Show parent | Reply

Yes it will eventually make it into core once but not until after the move to moodle 2 and after we fix the cross database issues. The large client is for Kineo US so you should be able to ask them directly for access if you want to look at it sooner.

If you made the batch insert change to the dynamic cohorts we'd definitely look at getting it into core.

Simon

Permalink | Show parent | Reply