Make this query more efficient.

Question

How can I make this Jira query more efficient?

project = "Project Name" AND status != Cancelled AND status != Done AND status != Closed AND status != Deployed AND (issuetype != Feature OR issuetype != Sub-Task OR issuetype != Theme) AND ("Epic Link" = ZOSVPC-11 OR "Epic Link" = ZOSVPC-8 OR "Epic Link" = ZOSVPC-44 OR "Epic Link" = ZOSVPC-117 OR "Epic Link" = ZOSVPC-54 OR "Epic Link" = ZOSVPC-7 OR "Epic Link" = ZOSVPC-46 OR "Epic Link" = ZOSVPC-55 OR "Epic Link" = ZOSVPC-5 OR "Epic Link" = ZOSVPC-56 OR "Epic Link" = ZOSVPC-22 OR "Epic Link" = ZOSVPC-55) OR issuetype = Risk AND labels = Dependency AND priority != Low AND priority != Medium AND priority != Lowest AND status != Closed ORDER BY "Epic Link" ASC

Phill Fox · Answer

Hi @amy schneider

In addition to the suggestions from @arie.holleman which are all very sensible and will help you greatly you might want to consider if you could use the resolution field in place of

status != Cancelled AND status != Done AND status != Closed AND status != Deployed

which using the logic from Arie would have become

status not in (Cancelled, Done,  Closed, Deployed)

I am assuming that you set the resolution field ONLY when an issue transitions to Cancelled, Done,  Closed, Deployed in which case this can be simplified down to

resolution is EMPTY

Using these simple techniques you can have

project = "Project Name" AND resolution is EMPTY

AND issuetype NOT in (Feature,Sub-Task, Theme) AND "Epic Link" In ( ZOSVPC-11, ZOSVPC-8 ,ZOSVPC-44 , ZOSVPC-117 , ZOSVPC-54, ZOSVPC-7 ,ZOSVPC-46 , ZOSVPC-55 ,ZOSVPC-5 , ZOSVPC-56 , ZOSVPC-22 , ZOSVPC-55)

OR issuetype = Risk AND labels = Dependency

AND  priority in (high, highest)

ORDER BY "Epic Link" ASC

However this reveals a quirk in your logic about what are you expecting to happen with

OR issuetype = RISK  AND labels = Dependency

Are you expecting these risks to only be in the single project or across all projects?  Also are you expecting labels = Dependency to only apply to risks?

I have been bold and suggest that this is what you were aiming to achieve if it is all items associated with your list of epics plus any risks that are not yet resolved. .

project = "Project Name" AND resolution is EMPTY

AND

(

(issuetype NOT in (Feature,Sub-Task, Theme)  AND "Epic Link" In ( ZOSVPC-11, ZOSVPC-8 ,ZOSVPC-44 , ZOSVPC-117 , ZOSVPC-54, ZOSVPC-7 ,ZOSVPC-46 , ZOSVPC-55 ,ZOSVPC-5 , ZOSVPC-56 , ZOSVPC-22 , ZOSVPC-55))

OR

(issuetype = Risk AND labels = Dependency)

)

AND  priority in (high, highest)

ORDER BY "Epic Link" ASC

For clarity I split the command across multiple lines to make it easier to read but you will find it easier to put it in the advanced JQL filter if you remove the line breaks.

Hope this helps

Phill

arie.holleman · Answer

Hi Amy,

you can use “in” instead of all the or statements. For example status not in(cancelled, done) and so on.

Another improvement can be to change from != to = statements. On priority you might end up with less comparisons. For example, priority in (high, highest)

I hope this helps!