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.
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.
To give you an example on two queries, one being optimized and the other not. Have a look at the following two queries:
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.
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.
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