JQL query optimisation

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

This widget could not be displayed.
Tarun Sapra Community Champion Oct 15, 2014

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

 

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
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Aug 06, 2018 in Jira Service Desk

A is for Activate: Share your top Jira Service Desk onboarding tips for new users!

Hi, everyone! Molly here from the Jira Service Desk Product Marketing Team :).  In the spirit of this month's  august-challenge, we're sourcing stories of Jira Service Desk activation fro...

576 views 25 15
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you