JQL query optimisation

Wessel Venter October 15, 2014

Hi, I would like some advice about optimising a JQL query.  The query selects all issues of a specific subset which have been in the Waiting for Input state for more than a week but less than two weeks.  This is information which I require on a regular basis for reporting purposes.  The query is as follows:

project = ABC AND "Assigned Team" = DEF AND status = "Waiting for Input" AND status changed to "Waiting for Input" before "-7d"  AND (status changed to "Waiting for Input" after "-14d"  OR status changed to "Waiting for Input" on "-14d" ) AND NOT (status changed to "Waiting for Input" after "-7d"  OR status changed to "Waiting for Input" on "-7d" ) ORDER BY priority DESC, created ASC

This query returns the correct result (I have not been able to simplify the query without it including false positives), but it takes a long time to complete (I just timed it as 100 seconds).   It takes so long, that the query seems to time out both in the search bar on the JIRA website and when issued as a REST API call.  The query does succeed, however, if it is embedded in a dashboard.

My goal is to have this query not time out so that I can access its results through a REST API call.

The thing is, in the test which I just ran, the following part of this query returns 6 results and does so within a couple of seconds:

project = ABC AND "Assigned Team" = DEF AND status = "Waiting for Input" ORDER BY priority DESC, created ASC

The missing part of the query would further reduce the results to two.  And so I am baffled that all the results in the DB are whittled down to a handful of issues in such a short time, and then an (admittedly expensive) operation on that small result set takes so long.  All that I can conclude is that either the query does not execute strictly left-to-right, or it does, but the required information is in another table and that whole table needs to be scanned.

I believe that I need to do this date/age range checking in the query, as I have not been able to find the necessary information in the JSON result set which gets returned.

Can anyone please provide some guidance?  I am an end-user and not an administrator.

1 answer

1 accepted

0 votes
Answer accepted
Tarun Sapra
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 15, 2014

Can you give this a try - project = ABC AND "Assigned Team" = DEF AND status WAS IN "Waiting for Input" during ("-7d","-14d")

 

Wessel Venter October 15, 2014

Hi @Tarun Sapra, thanks for the answer! This would give false positives. In fact, I have just discovered that my query above gives false positives as well. :-/ But your answer gave me some ideas, so I have now solved the problem with this query: project = ABC AND "Assigned Team" = DEF AND status = "Waiting for Input" AND status changed to "Waiting for Input" DURING (-14d, -7d) AND status was not Open after -8d ORDER BY "Assigned Team" ASC, priority DESC, created ASC While it still takes "long" in my opinion, it now completes before the timeout and the REST API call returns a result. Thanks for the inspiration!

Suggest an answer

Log in or Sign up to answer