6.4.1, 7.3.3, 7.0.0, 8.5.1, 8.13.10, 8.20.3
Severity 2 - Major
Atlassian Update – 25 April 2023 Dear Customers, Thank you for taking the time to file and comment on this issue. We realize it still occurs and impacts your organization. We are now working on multiple customer requests and on new features, so we have to postpone our resolution of this issue. We’ve decided to move this issue to our long-term backlog. Please continue watching this ticket for future updates and changes in the timeline that impacts your work. Best regards Daniel Dudziak Senior Software Engineer
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).
Many values in custom fields options:
Steps to reproduce
- Create a MulitSelect custom field
- Add many values to it (e.g. 1000 or 10'000).
- Add the MultiSelect custom field to the project screens (to be able to set it for an issue).
- Restart JIRA
- Query issue update via rest.
- Enable SQL logging
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.