Performance: get Subtasks for a list of issues in environments with remote database (DC)

I’m currently working on an performance issue in a datacenter environment (Database on a remote host) where a lot of database queries cause a great delay while process a list of issues and their subtasks. (in total between 5k and 10k issues to process).
We are currently using the method, that I wanted to improve as it’s called for every Issue instead for a list of issues

issue.getSubTaskObjects()

Unfortunaly I was not able to find a Java API replacement that is capable to handle a list of issues instead of a single issue. Even the JQL is using a single query for each issue instead of a single query

parent in (PROJ-1, PROJ-2, PROJ-3)

Do someone know of a method that could be used to query the subtasks for a list of issues with a reduced amount of database queries?

As it turns out, there are a lot of unexpected database queries depending on how you do your JQL queries and how you use the jira objects.

parent in (PROJ-1, PROJ-2, PROJ-3)
=> 3 database queries if I remember correctly (Select the id of each given issue key in a separate query)

parent in (10001, 10002, 10003)
=> no database queries

To be able to get the needed information with much less database queries, I do use the SearchProvider with a custom Collector based on the following repository by atlassian:
https://bitbucket.org/atlassianlabs/jira-examples/src/master/src/main/java/com/atlassian/jira/examples/collector/

This way I managed to reduce the loading times in a first working example (10k issues +) from ~25s down to 5s

2 Likes