JQL query optimisation

Question

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 · Answer

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 · Answer

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..

Thomas Schlegel · Answer

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