JIRA-7.12.3 - 15 Single Select Custom Fields with 2500+ options in each - Edit Screen Very Slow

[Ramakrishnan Srinivasan]

Hi,

 I need some help. We have "Feature Request" issue type which addresses customer requirements. So, I have 15 Custom project pickers and one Single Select Branch field associated for each of those project pickers. These Branch fields are with 2500+ options in each (15 x 2500). When the Project picker value changes, I am able to load only that product specific branch names to the Branch custom field using script runner behavior

The problem is, if these Branch fields are present in the Edit Screen, it takes ~20 seconds to load the Edit and even Edit operations are very slow. 

Because these were Single Select custom fields, I thought some issue and tried Single Version Picker loading same 2500+ options in the project where customer requests are made. Still, response is very slow.

My question is, is that too much to have that many options in one custom field and many such custom fields. Ours is a JIRA Server version 7.12.3.

Any input or suggestion on how to manage large number of options during Create/Edit operation would really be helpful for us.

Thank you

with warm regards

ramki

[Ramakrishnan Srinivasan · Accepted solution]

I solved it with steps below

• Deleted all the 15 Single Select Branch Custom Fields
• Picked-up one Project "ProjectX" which by itself does not have Versions
  • ProjectX is the one where my Requirements are created and for the solution we have to have Product 1 to Product 15 project picker custom fields and their Branch fields
  • Other Project Versions are used as Branch values in ProjectX for Product 1 to Product 15
• Loaded "ProjectX" with Versions from all other Projects which are required for this workflow (this ~2700 Versions in ProjextX)
• Created 15 Branch Fields as Single Line Text Field and added them to ProjectX Create/Edit Screens
• Set up a script-runner REST End Point - frnversions
• Set up a script runner Behavior to convert Text to SingleSelect
• I replaced some names by find and replace, so if any error, it will be due to this change; the scripts were tested in my production environment
• Response now is much faster; 
• Further ask is whether it is possible to show some 10 values as default if user clicks on the down arrow on the field in Create/Edit screen

To load versions from other projects to my "ProjectX"; filtered by project category

import org.apache.log4j.Level
import org.apache.log4j.Logger
def log = Logger.getLogger("com.acme.workflows")
log.setLevel(Level.DEBUG)
// script is used to create master list of all versions from ProjectCategory_ABC project categories - Versions in ProjectX Project
// ProjectX Versions are set as Branch values from Product 1 to Product 15 using respective Branch fields

// this script has to be run as service
// Admin - System - Services - New Service and give the file path like
// //var/atlassian/application-data/jira/scripts/service_ProjectX_Primary_Product_Branch_Create_Options.groovy
// set the service duration to once in 4 hours every day - we can change later
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.project.version.VersionManager
import com.atlassian.jira.project.ProjectManager
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.customfields.option.Options
import com.atlassian.jira.issue.customfields.option.*

def ProjectXManager = ComponentAccessor.getProjectManager()
def ProjectX = ProjectXManager.getProjectObjByKey("ProjectX") // change project name here
//log.info(ProjectX.id)
def versionManager = ComponentAccessor.getVersionManager()

def ProjectX_current_versions = versionManager.getVersions(ProjectX.id)
//log.info(ProjectX_current_versions)
def customFieldManager = ComponentAccessor.getCustomFieldManager()
def projectManager = ComponentAccessor.getProjectManager()
def projects = projectManager.getProjectObjects()

def filteredProjects = projects.findAll{ thisProject ->
   if(thisProject.key != 'ProjectX') {
      if(thisProject.projectCategoryObject) {
         if(thisProject.projectCategoryObject.name in ["ProjectCategory_ABC"]) {
              def project = projectManager.getProjectObjByKey(thisProject.key)
              // https://dzone.com/articles/groovy-goodness-turn-a-map-or-list-as-string-to-ma
              // by filtering in next line, we get from ProjectX only this project key specific versions and see the match before creating
              def filtered_ProjectX_current_versions = ProjectX_current_versions.findAll{it.name.split(":")[0]==(String) thisProject.key}.toListString()
              def thisProjectVersions = ComponentAccessor.getVersionManager().getVersions(project)
              thisProjectVersions.each { thisProjectVersion -> 
                   def thisOption = (String) thisProject.key + ": "+ thisProjectVersion
                   if(!filtered_ProjectX_current_versions.contains(thisOption)) {
                       //log.info(thisOption + " option is NOT present --- will be added")
                       //if thisProjectVersion is NOT already contained in ProjectX Versions, create it now
                       versionManager.createVersion(thisOption, null, null, null, ProjectX.id, null)
                    }
              }
          }
       }
    }
}

REST END Point

// https://community.atlassian.com/t5/Jira-questions/JIRA-Custom-Field-large-Select-List/qaq-p/369492

// https://scriptrunner.adaptavist.com/5.4.43/jira/behaviours-conversions.html

// https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html#_querying_the_current_jira_database

// http://10.10.224.61:8087/rest/scriptrunner/latest/custom/frnversions?query=AGCM

// frnversions is the REST End point name

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.config.database.DatabaseConfigurationManager
import com.atlassian.jira.config.database.JdbcDatasource
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.sql.GroovyRowResult
import groovy.transform.BaseScript

import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
import java.sql.Driver

import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

@BaseScript CustomEndpointDelegate delegate

frnversions(httpMethod: "GET") { MultivaluedMap queryParams ->

def query = queryParams.getFirst("query") as String
def rt = [:]

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default")

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)

 try {
    sql
    def rows = sql.rows("SELECT vname FROM projectversion WHERE project=(SELECT id FROM project WHERE pkey='ProjectX') AND vname ILIKE ?", ["%${query}%".toString()])
    //def rows = sql.rows("SELECT vname FROM projectversion WHERE project=(SELECT id FROM project WHERE pkey='ProjectX') AND vname='FEAT 1.0'")

    rt = [
           items : rows.collect { GroovyRowResult row ->
           [
             value: row.get("vname"),
             html: row.get("vname").replaceAll(/(?i)$query/) { "<b>${it}</b>" },
             label: row.get("vname"),
            ]
            },
            total: rows.size(),
            footer: "Choose Selected Product Branch... "
           ]

} finally {
    sql.close()
     conn.close()
}

return Response.ok(new JsonBuilder(rt).toString()).build()
}

Behavior for those 15 Branch Fields - the whole thing should be loaded in initialiser section of the behavior window

getFieldByName("Product 1 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 2 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 3 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 4 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 5 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 6 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 7 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 8 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 9 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 10 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 11 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 12 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 13 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 14 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])

getFieldByName("Product 15 Branch").convertToSingleSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/frnversions",
query: true,
formatResponse: "general"
]
])