How i can fix slow JQL?

[Ruslan]

Hi, guys. 

How i can change my JQL, maybe you know. 

I need to give issue for sprint. My JQL look like :

"issueFunction in incompleteInSprint(${key},${it.id}) OR issueFunction in completeInSprint(${key},${it.id})")

then key = id board ; it.id = id spint 

my scrpt working more 1 minet andd i see warn:

2022-08-03 10:40:40,503 WARN [providers.LuceneSearchProvider_SLOW]: LuceneQueryExecutionEvent{query={issueFunction in incompleteInSprint(1, 13132)} OR {issueFunction in completeInSprint(1, 13132)}, queryTermMetrics={type:10812=TermMetric{count=2, isCustomField=false}, type:10802=TermMetric{count=2, isCustomField=false}, type:10813=TermMetric{count=2, isCustomField=false}, type:10502=TermMetric{count=2, isCustomField=false}, type:10810=TermMetric{count=2, isCustomField=false}, type:10817=TermMetric{count=2, isCustomField=false}, type:10803=TermMetric{count=2, isCustomField=false}, type:10407=TermMetric{count=2, isCustomField=false}, com.onresolve.jira.groovy.jql.IssueIdsQuery=TermMetric{count=2, isCustomField=false}, type:11001=TermMetric{count=2, isCustomField=false}, type:11100=TermMetric{count=2, isCustomField=false}, type:11000=TermMetric{count=2, isCustomField=false}, type:11600=TermMetric{count=2, isCustomField=false}, type:5=TermMetric{count=2, isCustomField=false}}, numberOfClausesInQuery=28, executionTime=823, numberOfResults=10, collectorType='TopDocs'}

maybe you have another solution

[Nic Brough -Adaptavist- · Accepted solution]

Those functions can be very slow, especially when you're working with a large data set, and looking for data that is not indexed (in this case, the board relationship with an issue is not indexed, so you're making Jira work hard to look it up)

I would change the query completely. 

The results you should get from the query can be described as "all issues in sprint X".  So I would move to using simply "Sprint = X"

[Ruslan]

Thanks Nic, but i have one problem. 

I have different issues array then i use Sprint = X , or "issueFunction in incompleteInSprint(${key},${it.id}) OR issueFunction in completeInSprint(${key},${it.id})")

i tryed undestand when right option.  and i think Slow jql give me a true,but i am not sure 

[Nic Brough -Adaptavist-]

I don't understand why you are constructing such a complex query when you do not need to.

Could you write the question you are trying to ask in plain English (not JQL or expressions).  Something like "I want a list of all issues that are in this sprint"

[Ruslan]

I am creating a sprint team performance report. I take all tasks, and unconmplitman tasks in a sprint. And I calculate their story points.

SPrint = X , give me issue who didn't even make it in this sprint, they were just once attached to it and thrown out before it started

[Nic Brough -Adaptavist-]

No, it does not.  I've just tested this to make sure, as you cast some doubts into my mind with that, but sprint = X does return issues as I expected it to.

Setup a backlog so that I have a number of issues, of which we're only going to care about AA-1 and AA-2.  Create empty sprints sprint-1 and sprint-2

Action	Search for "Sprint = sprint-1"	Search for "Sprint = sprint-2"
Move AA-1 into sprint-1	AA-1
Move AA-2 into sprint-1	AA-1, AA-2
Move AA-1 to backlog	AA-2
Move AA-1 back in	AA-1, AA-2
Start sprint	AA-1, AA-2
Move AA-1 to backlog	AA-2
Move AA-1 back into sprint	AA-1, AA-2
Mark AA-1 as done, close sprint	AA-1, AA-2	AA-2

From the issue point of view, the sprint field for AA-1 is now "sprint-1", but the field for AA-2 is both "sprint-1" and "sprint-2"

Sprint = X will return a list of issues that are in a sprint (whether planned or active)), or were in a sprint when it was completed.

My guess is that there is something missing from what you've defined for us.  I don't think you mean just "show me everything in a sprint", I think you mean "in the current active sprint"

[Ruslan]

I keep looking for it. Anyway you gave me an alternative, thanks a lot