How to find Oracle DB Query mapped to a given JQL Query?

Question

Hi

Is there a way to get the exact Oracle Database query executed as a result of running a particular JQL Query?

Currently we have identified some JQL queries takes more time than the expected. Idea is to find the ways of improving JQL query execution time. Appreciate your feedback.

Regards

PIyal

Piyal Nanayakkara · Answer

Hi

Sorry for the confusion. Please discard earlier elapsed times.

Here are the latest results

For the full query Elapsed Time : 207873 ms  (around 3.5 minutes) and here are Elapsed Times for specified queries

1.  3517 ms  (3 seconds)
2. 14063 ms  (14 seconds)
3. 1510 ms  (1.5 seconds)

Nic Brough -Adaptavist- · Answer

You've contradicted yourself here, so I am now confused.  At first, the query was slow at 4 minutes, now it's fine at 102ms.  Which is it?  Is there still a problem?

Piyal Nanayakkara · Answer

Thanks Nic

I was able to capture the elapsed time for the  3  scenarios pointed by you and the elapsed time is given. I have ran this in my local dev environment.

1. 64 ms

2.  61 ms

3. 97 ms

The elapsed time for the full JQL query is 102 ms

Here is the Script (for the 3rd scenario) I ran in the Jira Script console

//======================================================

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.web.bean.PagerFilter

def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchProvider = ComponentAccessor.getComponent(SearchProvider)
//def issueManager = ComponentAccessor.getIssueManager()
def user = ComponentAccessor.getJiraAuthenticationContext().getUser()

// edit this query to suit
def query = jqlQueryParser.parseQuery("project = 'CQDS' AND issuetype = 'Bug' AND component in ('Mastering', 'Mastering_CustomMedia', 'MasteringA&P', 'MasteringAstronomy', 'MasteringBiology', 'MasteringChemistry', 'MasteringEngineering', 'MasteringEnvironmentalScience', 'MasteringGenetics', 'MasteringGeography', 'MasteringGeology', 'MasteringHealth', 'MasteringMeteorology', 'MasteringMicrobiology', 'MasteringNutrition', 'MasteringOceanography', 'MasteringPhysics', 'Modified Mastering') AND created >= '2018-01-27' AND created <= '2019-02-26' order by cf[11330] ASC")
def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter())

//======================================================

I will get this executed in production environment.

Regards

Piyal