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.

Testing instructions:

1. Create a large test course.
2. Create Grouping
3. Create Groups using auto-create groups.
   1. Set Group/member count to 1000.
   2. Select Grouping of auto-created groups, previously created grouping.
4. Create Workshop activity module.
   1. Common module settings > Group mode : Separate groups.
   2. Common module settings > Grouping : Select previously created grouping.
   3. Save and display.

Actual results without the optimization:

Workshop activity module is not loading and throwing 504 error, or it takes a lot of time to load a content

Actual results with the optimization:

Workshop activity module loads faster.

Expected results:

Workshop activity module should load