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

[Piyal Nanayakkara]

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

[Nic Brough -Adaptavist-]

Most JQL does not cause any SQL to be run.  JQL runs its searches against the local index, which is a copy of the database, but in a totally different structure, so even if it were using SQL against the index (which it doesn't), it would be very different to what you would need to run in the Jira database.

You will need to look elsewhere for the cause of slow queries - we might be able to tell you more if you gave us the JQL you're running (feel free to obscure any data - we're interested in the structure of the query, the field types and functions in use)

[Piyal Nanayakkara]

Thank Nic for the response.

Here is a slow JQL query which takes about 4 minutes to run

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

Regards

Piyal

[Nic Brough -Adaptavist-]

How many issues does this return, and how many are in the project overall?

Does chopping bits out of the query make it faster (I know this makes it less useful, but we're looking for a point of slowness, not a working query).  For example, how do

• Project = CQDS and issuetype = bug
• Project = CQDS and issuetype = bug AND created >= "2018-01-27" AND created <= "2019-02-26"
• 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")

compare in speed?

[Piyal Nanayakkara]

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

[Nic Brough -Adaptavist-]

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]

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)