JQL Tips and Tricks

16 november 2017
Jira

Atlassian offers a very powerful tool in JIRA; Advanced Issue Search with JQL. As TMC consultants, we see this functionality used in almost every company we visit. The advantage of JQL is that you do not need a technical background and we see al lot of people using it. Unfortunately, there is also a disadvantage. JQL queries can become slow when not correctly defined or setup to complex. Result in slow responding dashboards containing multiple queries/filters. But if you just take a few things in mind, you will become a JQL boss.

Sorting Functionality

Most people use the “order by” to sort on a specific column. When the search result contains a lot of issues, only the first fifty are shown at the first page. To find out which are the first fifty, the complete result has to be sorted first. When using this result in a gadget, the sort method easily can be left out.

Example

To give you an example on two queries, one being optimized and the other not. Have a look at the following two queries:

project=TMC and issue in parent("status in(verified, released, closed, deployed)")
issue in parent("project=TMC and status in (verified, released, closed, deployed)")

Which one would be fastest performing?

The second query is much faster. The “issue in parent” function is very costly, as you need logic to find the issues instead of just filtering “basic” field. The query between the brackets is done first. In the first query, all issues which matches one of the statuses are selected first over all the projects. At last, only the issues which match the TMC project are selected. The second query does all the selection first, and then the costly function “issue in parent” is called.

There are add-ons at the marketplace available which custom JQL functions and some customers write there own. They can be powerful, but use them wisely.

Another example

The third and last item is permission checking. If you have only access to one project, there are still two ways of writing a query.

summary ~ blog
project = TMC and summary ~ blog 

Which one would be fastest?

The second query is much faster as the first query will check every available project for “browse permission”. It is recommended to select on project whenever you can.

Please keep in mind these three items when writing JQL queries and benefit from all the nice things Atlassian has to offer. Keep your IT department and all other users as happy as you are by having fast running queries.

Author: Bert van Dijk