JQL query optimisation

[Molamma xavier]

Hi, I would like some advice about optimising a JQL query. 

Now i have using the below Jira JQL query in confluence, 

Project = "IMS DevSupport" AND status in (Closed, Resolved) AND filter in (152387, 140069, 140048) AND created >= startOfMonth(-3) AND created < startOfMonth(-2) 

While editing the filter to below i have getting time out error.

Project = "IMS DevSupport" AND status in (Closed, Resolved) AND filter in (152387, 140069, 140048) AND resolved >= startOfMonth(-3) AND resolved < startOfMonth(-2) ORDER BY resolution ASC

please help me to make the query to runs in 5 sec.

[Davin Studer]

I'm not sure if this applies to JQL as well, but I know when it comes to something like SQL if you try and limit as much data as possible first you will get better performance. So, you could try moving the resolved filter closer to the beginning of you JQL query.

[Mirek]

Hi @Molamma xavier! If you are talking about JQL it is definitely JIRA, not Confluence :) since JQL = JIRA Query Language.

Your query is using this part ...

filter in (152387, 140069, 140048)

which is the most affecting and time consuming task. We do not know what is behind those filters and those could be very slow (a lot of data). Without full picture it is hard to optimize your JQL and guarantee that it would run in 5s.

Maybe you can describe what you would like to search for and that is how we would be able to help you.

[Molamma xavier]

Hi Mirek,

Thanks for your response.

I have using the jira filter query in confluence jira issue macro.

There is already one bug in confluence,

https://jira.atlassian.com/browse/CONFSERVER-53469

As per the vendor suggestion need to reduce the running time to 5 sec.

so i need to reduce the running time of below query to 5 sec by optimizing the query.

Project = "IMS DevSupport" AND status in (Closed, Resolved) AND filter in (152387, 140069, 140048) AND resolved >= startOfMonth(-3) AND resolved < startOfMonth(-2) ORDER BY resolution ASC

[Thomas Schlegel]

Is the performance also so bad when you run the query directly in Jira? 

[Mirek]

As I mentioned.. It is hard to optimize a JQL query if we do not know all information. What I can suggest is to start small and see when the query response is longer than 5s. Then you would see which element of the query is affecting and maybe would be able to optimize it. My guess it that "filter in (152387, 140069, 140048)" might be the "thing"

Start with this simple query and check if it is correctly showing in Confluence..

Project = "IMS DevSupport"

then this..

Project = "IMS DevSupport" AND status in (Closed, Resolved)

and later..

Project = "IMS DevSupport" AND status in (Closed, Resolved) AND resolved >= startOfMonth(-3) AND resolved < startOfMonth(-2) 

finally this ..

Project = "IMS DevSupport" AND status in (Closed, Resolved) AND resolved >= startOfMonth(-3) AND resolved < startOfMonth(-2) ORDER BY resolution ASC

If all would be correctly showing then you have a proof. Later you would need to spend time and optimize those 3 filters with IDs 152387, 140069 and 140048..

But in my personal opinion it might be hard to achieve if you are having complex filters behind those IDs..