How to optimize JQL queries, are there any guidelines ?

Sridhar Nimmagadda June 30, 2015

I have performance issues and it boils down to JQL's so are there any guidelines to tune JQL's , starting with some obvious low hanging fruits

3 answers

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

2 votes
crf
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
June 30, 2015

We have a Knowledge Base article called Understanding JQL Performance that may shed some light here.  In addition, I will add that:

  1. If a function takes a project argument and it makes sense, then you should probably use it even if it is redundant.  The way that the functions work means that they aren't generally aware of whether or not the project was specified elsewhere, and this can affect the amount of extra work they have to do.
  2. As tempting and sensible as it might seem, you should avoid using "filter IN" to amalgamate smaller views into larger team views.  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.  Find 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.
  3. 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.

Hope that helps!

1 vote
João Palharini
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
June 30, 2015

Sridhar,

JQL searches are normally well-optimized by JIRA's indexes, so there aren't many hints on how to optimize them other than try not to have too many filters returning a very large number of issues. As much as you can narrow down the searches, the better your instance will perform.

Also, it's important to be careful when searching for custom fields with calculations or very large amounts of information and using JQL-related add-ons.

--
Cheers!

Joao

Sridhar Nimmagadda July 2, 2015

Well I have a lot of text searches using ~ will there be any impact of these or something like text ~ "something" which searches all the text fields

crf
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
July 2, 2015

Yes. Comments (and as of 7.0, worklog comments as well) are kept in a separate index from the issue data. This means that we have to "join" across the separate Lucene indexes. But Lucene is not a database and this "join" can be comparatively expensive, particularly when there is a large number of hits. For every comment in the whole JIRA instance that uses that word, we have to collect the issue ID into a set and use it to filter the result from the rest of the query. For a single query, this is not noticeable. If you are running multiple JQLs or ones that are nested (see the answer I gave), the consequences can be more noticeable. If you are really only interested in searching the summary and description, then you should explicitly state that in the search. In general, JQL text ~ searches are most likely to be useful as a one-off when you are a person looking for a particular issue not as part of a rapid board filter or something similar. For those cases, using a label makes a lot more sense.

Sridhar Nimmagadda July 3, 2015

Thanks Chris !! So you are saying only comments are kept in a separate index, all other customfields (text, numeric,date) and system fields are kept together in issue indexes..if i can localize my query to only custom fields (text or other) or system fields.. the performance should be acceptable. There is no performance impact of using text fields if i am not hitting comment indexes ?

0 votes
Midori
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 30, 2015

I don't think such thing exists.

JQL, in this sense, cannot be compared to SQL. You don't have joins, full-table-scans, indexes, etc. so there is no toolset you could use to optimize. JQL is just a complex textual search criteria.

Having said that, I'd make sure that there is no redundant condition in the JQL. For instance, if you filter against an issue type which exists only in one project, then do not redundantly filter against the project. I don't know if it produces noticeable performance improvements, but at least simplifies your queries. 

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

TAGS
AUG Leaders

Atlassian Community Events