Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-60532

Slow query in user picker : DefaultUserPickerSearchService

    XMLWordPrintable

Details

    Description

      NOTE: This bug report is for JIRA Server. Using JIRA Cloud? See the corresponding bug report.

      Summary

      The DefaultUserPickerSearchService builds a query that is very slow to run on instances with many users. It seems to run faster for searches on short usernames compared to searches on long usernames (e.g. a search for "b" runs much faster than a search for "bhas").

      This causes features such as mentions to perform poorly on instances with many users (e.g. JAC).

      Environment

      This performance problem is more evident on instances with many users, such as JAC. For local testing, you can use the JIRA Data Generator Plugin to create many users (about 50,000 is necessary to start seeing a significant problem).

      Steps to reproduce

      1. Hit an endpoint which uses the user search service e.g. https://jira.atlassian.com/rest/api/2/user/viewissue/search?username=bhas&issueKey=JRA-59658

      Expected result

      Response comes back in less than 100ms.

      Actual result

      Response comes back in ~2.5s

      Notes

      DefaultUserPickerSearchService ends up creating the following query:

      SELECT ID, directory_id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, CREDENTIAL, deleted_externally, EXTERNAL_ID FROM PUBLIC.cwd_user WHERE (((lower_user_name LIKE  ? ) OR (lower_user_name LIKE  ? ) OR (lower_user_name LIKE  ? ) OR (lower_user_name LIKE  ? ) OR (lower_user_name LIKE  ? ) OR (lower_user_name LIKE  ? ) OR (lower_user_name LIKE  ? ) OR (lower_user_name LIKE  ? ) OR (lower_user_name LIKE  ? ) OR (lower_display_name LIKE  ? ) OR (lower_display_name LIKE  ? ) OR (lower_display_name LIKE  ? ) OR (lower_display_name LIKE  ? ) OR (lower_display_name LIKE  ? ) OR (lower_display_name LIKE  ? ) OR (lower_display_name LIKE  ? ) OR (lower_display_name LIKE  ? ) OR (lower_display_name LIKE  ? )) AND (active =  ? )) AND (directory_id =  ? ) ORDER BY lower_user_name LIMIT 100

      Where each LIKE is prepending a separator to the search query e.g. if you search for "Broo" it will create a LIKE for: " Broo", "-Broo", "(Broo" etc. This is so a search for "Broo" will return a user like "Mike Cannon-Brookes". The query above can be even worse if the search is also including results for users whose email address matches, as it will add another 8 LIKE clauses to the above query.

      Workaround

      There is no workaround for this issue.

      Attachments

        Issue Links

          Activity

            People

              prunge Peter Runge (Inactive)
              bhaswell Blake Haswell (Inactive)
              Votes:
              38 Vote for this issue
              Watchers:
              60 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: