Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-44588

Multi Select/ Cascading Select List Custom Field loads options in a suboptimal way

XMLWordPrintable

    • 6.04
    • 55
    • Severity 2 - Major
    • 136
    • Hide
      Atlassian Update – 5 July 2024

      Dear Customers,

      I am more than happy to announce that the fix to this bug was released in Jira 9.17.0 on the 26th of June 2024. It will be considered for backport to the 9.12.x LTS version after ~6 weeks from the 9.17.0 release.

      Best regards

      Agata Kowal
      Software Engineer Jira DC

      Show
      Atlassian Update – 5 July 2024 Dear Customers, I am more than happy to announce that the fix to this bug was released in Jira 9.17.0 on the 26th of June 2024. It will be considered for backport to the 9.12.x LTS version after ~6 weeks from the 9.17.0 release. Best regards Agata Kowal Software Engineer Jira DC

      JIRA issue update via rest on cold caches may take significant amount of time (15 min on a customer instance). Subsequent issue updates take reasonable amount of time (~30 seconds).

      Environment
      Many values in custom fields options:

      select count(cfo.id),customfield.cfname from customfieldoption cfo join customfield on customfield.id=cfo.customfield group by customfield.cfname order by count(cfo.id) DESC limit 10;
      

      Steps to reproduce

      1. Create a MulitSelect custom field
      2. Add many values to it (e.g. 1000 or 10'000).
      3. Add the MultiSelect custom field to the project screens (to be able to set it for an issue).
      4. Restart JIRA
      5. Query issue update via rest.
      6. Enable SQL logging

      Result
      Issue update executes 1000 (or 10'000) requests to the database (see in SQL log). One per each value of a multi select.

      Expected Result for Select
      We execute only single SQL query as MulitSelect or Select can not have nested values.

      Expected Result for CascadingSelect
      We execute requests in batches, effectively reducing the numbers we query the database.

      The root of the problem
      Upon update operations, Multi Select loads all the data to validate the update. It browses all the fields that are defined for the issue and is checking whether we attempting to set any illegal value to a field.

      The code to load Multi Select List and Cascading Select List is shared. Actually sharing the code makes loading of values sub-optimal for a Multi Select List.

      In Multi Select List - for each of the values, we verify whether we have any nested options, it's actually impossible to have ones for a Multi Select List (but is possible for Cascading Select List).

      It means that if we have a number N of Mulit Select Lists on a page with a number of Values V we will end up
      N*V queries to the database to warm up the cache.

      Ideas on how to fix it:

      • Make a single query to DB/Cache to get the batch result for the children count, to evict values with no children. This should statistically make it faster.
      • Pass some context in the CustomFieldOptionJsonBean so it will not attempt to query children to Multi Select List (as it can not have nested values, i.e. no children size is always 0).
      • Eliminate code sharing between Multi Select List and Cascading Select List, for Multi Select List to load it's values in efficient way.

              e2656c68d96d Agata Kowal
              ialexeyenko Ignat (Inactive)
              Votes:
              28 Vote for this issue
              Watchers:
              53 Start watching this issue

                Created:
                Updated:
                Resolved: