Jira JQL Queries takes more time when there is a date criteria

[Piyal Nanayakkara]

Hi

I have observed that Jira JQL Queries takes more time to execute when there is a date criteria based on the atlassian-jira-slow-queries.log file

Eg: Below log entry shows that the JQL query took approx, 2.4 seconds to execute 

2019-04-23 00:17:12,603 http-nio-8090-exec-298 INFO VBOMMLA 17x6072816x5 axdmc6 /rest/gadget/1.0/issueTable/filter [c.a.j.i.search.providers.LuceneSearchProvider_SLOW] JQL query '{project = "CRMSFDC"} AND {issuetype = "Bug"} AND {labels = "Production_Known_Defects"} AND {created >= "2019-04-04"} AND {created <= "2019-04-26"}' produced lucene query '+projid:22552 +type:1 +labels_folded:production_known_defects +created:[
e@ TO *] +created:[* TO
f
@]' and took '2395' ms to run.

Seems like execution time depends on the Date range and the total no. of records in the below tables:

 - Project

 - JiraIssue

We are looking into the ways we can reduce the execution time of such queries by following approaches.

1) Identify the Projects/ Jira Issues which have not updated recently using below query

 SELECT Distinct p.pkey,p.LEAD,p.pname,p.URL,MAX(i.UPDATED) as "Last Updated"
 FROM jiraissue i
 INNER JOIN project p
 ON p.ID = i.PROJECT
 GROUP BY p.pkey,p.LEAD,p.pname,p.URL
 ORDER BY MAX(i.UPDATED) ASC

2) Archive the data in Project and JiraIssue tables which have not updated recently (lets say projects which were last updated 1 month ago or earlier than that.)

Appreciate your feedback on above approach and let us know whether there are any other options we can consider to speedup the JQL query execution time.

Regards

Piyal

[Colin McDermott_GLiNTECH]

I would be looking at your instance to start off with. 

Is your $jira_home on an SSD drive with good R/W speeds?  Remember Jira cache's it's query index in it's homedirectory. 

Have you done a locked reindex? Recreating the index will help optimize the Lucene storage. 

[Gonchik Tsymzhitov]

Hi, 

I would recommend you upgrade to Jira 8, where Lucene was updated