Summary

When an installation has lots of custom fields and a lot of projects, the permission checks for non-admin users cause the call /rest/gadget/1.0/statTypes to be slow. This call is used by the following gadgets:

• Two Dimensional Filter Statistics
• PieChart
• HeatMap
• Stats

Additionally, there is no AJAX timeout error handling for these gadgets. This causes an exception and the loading indicator is never removed.

Environment

Custom-Fields: 61x
Groups: 46xx
Issues: 1177xxx
Projects: 24xx
Users: 22xxx

Steps to Reproduce

1. Clear caches via HTTP DELETE on rest/internal/1.0/cache/app using HTTP basic authentication
2. Make the slow call via HTTP GET on rest/gadget/1.0/statTypes

Expected Results

Response within AJAX timeout (60 sec)

Actual Results

Call /rest/gadget/1.0/statTypes takes 2-3 sec for admin user and 112 sec for non-admin user.
Massive amount of SELECT like in Sql log:

2016-03-17 07:37:53,216 http-bio-18080-exec-19 user 457x5047x5 1pz5e75 /rest/gadget/1.0/statTypes 0ms "SELECT ID, PID, PROJECTROLEID, ROLETYPE, ROLETYPEPARAMETER FROM projectroleactor WHERE PID='28024' AND PROJECTROLEID='10202'"

Total is 16350 SELECTs:

Initial analysis

There are 3 nested loops

• For each custom field:
  • For each project the custom field is bound to:
    • For each permission scheme entry in this project matching the `BROWSE_PROJECTS` permission:
      • `SELECT ID, PID, PROJECTROLEID, ROLETYPE, ROLETYPEPARAMETER FROM projectroleactor WHERE PID='$projectId' AND PROJECTROLEID='$schemeEntryParameter'`

There is a short-circuit on each loop, ie. if the permission scheme entry matching `BROWSE_PROJECTS` for the first project for the first custom field says the user has the access, there will be only one `SELECT`.

Workaround

• For global custom fields, the project loop iterates over all projects sorted alphabetically by name (not key) in case-insensitive manner.
  To make the project loop short-circuit faster, a test project can be created, with a name, which would place it at the top of the alphabetically sorted project list, e.g. aaaa.
  The user, which suffers from the slowdown, should be given permission to Browse Projects for the aaaa project.
  This should reduce the number of SQL queries for global custom fields.

• In order to reduce the DB load for non-global custom fields, add the fields to the temporary project. However, all users with the permissions to the temporary project would gain access to all such custom fields.