The workshop activity module has a performance issue related to its method, get_users_with_capability_sql, which generates a poorly optimized SQL query. Currently, it uses a UNION to combine SQL queries, but this could be improved by implementing simple caching and avoiding complex UNION queries.

For instance, when there are 1000 groups, loading content becomes significantly slow and may even result in a 504 error due to timeout. I have attached a text file containing a SQL query which spans 15,000 lines, along with an optimized version of the SQL. Initially, I encountered a different problem that I couldn't replicate locally, but this example is the closest scenario I've managed to recreate the issue.

—

Edit (Conn Warwicker)

I have taken this ticket from Vlad now as he had to work on other things.

Here's what I've done:

• Removed the calls to some of the core functions to build up the SQL, as this was creating huge SQL statements, e.g. 80,000+ lines long in some cases, with endless UNIONS and other stuff which simply wasn't needed.
• Tried to streamline it all into 1 simple SQL query custom to the workshhop module
• I did initially play around with doing the bare minimum in SQL and then running users through `has_capability()` but it causes memory errors on very large courses.

Bench-marks:

(Purged cache before each test)

Pre-patch:

• Workshop view page: (25s)
• Workshop submissions allocation page [10 users per page]: (45s)

With-patch:

• Workshop view page: (9s)
• Workshop submissions allocation page [10 users per page]: (16s)

Screenshots:

pre-patch-view-page

pre-patch-allocation-page

with-patch-view-page

with-patch-allocation-page