Purpose

JIRA Users sometimes experience slow response to certain queries and JIRA administrators want to be able to guide their users on how to better write JQL that is better performing.

Solution

In general there is little that an end user can do to affect the runtime performance of a search in JIRA.  This article explains what the elements of performance are that affect JQL searching and gives some tips on how you might be able to avoid some pitfalls.

Performance components of JQL

The Parser

The JQL parser interprets the JQL string.  This has only a small impact on the search and is not an area to be concerned with.

Permission Checking

Permission checking can have a large impact on performance when searching.

When a user performs a search, that user will only see results for issues they are permitted to view.  If there are a very large number of permission schemes and projects or a large number of issue security schemes, then working out what issues a user is allowed to view can be quite time consuming.  The problem exists in two parts:

• what projects does the user have the Browse permission; and
• what issues are restricted by an Issue Security scheme and not viewable by the user as a result.

In general this is a difficult problem and all projects and permissions need to be considered. In some cases however it is possible for the system to work out that only certain projects can be in the results and in that case the gathering of all the permission information can be restricted to only those projects.  So a JQL of the form

PROJECT = MYT and summary ~ overheating

may perform better than one of the form

summary ~ overheating

JQL Functions

JQL Functions are evaluated before the search is performed and may be thought of as a "macro" in programming terms. Many customers write their own JQL Functions and many install plugins which supply additional JQL Functions.  Some of these may be very poorly performing.

A typical poorly performing function might be something like "issuesWithPdfAttachments()" and we could see a JQL like

Project = FRE and issue in issuesWithPdfAttachments()

Typically the function would query the issue attachments table in the database and select all those issues with a type of PDF and then combine this with the rest of the JQL, so we end up with  a JQL of the form

Project = FRE and issue in (12, 1212, 1213, 1223, 1224,1145, ...... (100,000 more in here)  ....., 987524 )

This behaviour is often very unexpected to the user writing the JQL.  The problems are that:

1. the gathering of all the issue identifiers is time-consuming and does not take into consideration the rest of the JQL, i.e. it knows nothing about "Project = FRE"; and
2. the work done by the Lucene parser and query engine is as a result quite more difficult.

If you are the query writer, you can try and be a bit smarter and perhaps require or, at least, allow the user to supply a list of projects to the function.  If it is a function from a third party developer then see if they can offer some improvement on how it works.

Sorting

In JIRA, most searches return only a limited number of results, e.g. one page of about 50 issues.  This seems then that the work done by the search should be small, but that is not always the case.  Most often the results are sorted. This leads to a number of areas where performance is a consideration:

1. All the results need to be sorted to find the first 50.  This is a slight exaggeration because JIRA is smart when sorting in this circumstance and discard results if it can quickly determine they are outside the top 50, but it still needs to consider them.
2. Sorting in JIRA is flexible and powerful, so if for example you sort by Project Version, then the order is determined by how the Versions are sequenced by the Project administrator.  This can change at any time, so the sorting has to be flexible enough to handle this. Similarly, if you sort by assignee this will be sorted by the Assignee's Full Name.  This requires additional lookups and computation.  The JIRA developers have spent a lot of time optimising the system fields and standard custom  fields in JIRA to make the sort performance of these very good, but if you add your own Custom Field Types or have additional Custom Field Types supplied by third party plugins these might have very poor sort performance.

Please note

• If a function takes a project argument and argument makes logical sense, the argument should be used it even if it is redundant. Functions operate in a manner prevents them from being aware of whether or not the project was specified elsewhere, and this can affect the amount of extra work they have to do.
  
  
• Using "filter IN" to amalgamate smaller views into larger team views Should be avoided.  These are effectively nested searches, so "filter IN (1, 2, 3, 4)" is not a single search; it is five complete searches that then filter the results of the first search with the results of the other four.  With multiple levels, this can get out of hand very quickly.  Finding a way to express what you want more directly and/or set up the relationship the other direction so that you are searching through single "parent" filters at each level rather than through multiple "child" filters is more efficient.
  
  
• Several functions can have very surprising side-effects.  Be particularly wary of ones that let you specify more JQL to test a field against, as it likely to be doing something similar to what "filter" does.