Uploaded image for project: 'Jira Software Data Center'
  1. Jira Software Data Center
  2. JSWSERVER-25517

Retrieving the value of Advanced Roadmap's Team custom field executes multiple slow SQL queries

XMLWordPrintable

      Issue Summary

      When the Team custom field value is fetched, it can result in up to 4 database queries being executed. This can be especially problematic in situations that retrieve the value for many issues, like exporting or viewing Agile boards with the Team field displayed on cards.

      Those SQL queries are related to permission checks. For example, if a team exists only in a single plan, we should only display it to users who can access that plan.

      This is reproducible on Data Center: yes

      Steps to Reproduce

      1. Create 1000 issues with a non-empty Team field from Advanced Roadmaps for Jira
      2. Go to Issues > Search for issues > Export > CSV (All Fields)

      Expected Results

      Fetching the value of the Team custom field has an acceptable performance

      Actual Results

      Fetching the value of the Team custom field is slow, and can involve 4 SQL queries.

      The slowest query is used to check in which plan the team is being used. It often involves a full table scan and includes a JOIN.

      Workaround

      • The permission checks for Shared Teams are significantly simpler, and they don't need to execute the two slowest queries. If changing your teams to shareable (which will make them visible to more users on the instance, and make them reusable across plans) is acceptable, it will improve the performance.
      • You can remove the need for a full table scan from the slowest query by adding an index to the TEAM_ID column in the AO_D9132D_PLANTEAM table:
        CREATE INDEX idx_planteam_team_id ON AO_D9132D_PLANTEAM (TEAM_ID);
        

        This won't fully solve the problem, as the query still performs a potentially expensive LEFT JOIN.

              drauf Daniel Rauf
              drauf Daniel Rauf
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Created:
                Updated:
                Resolved: