Slow loading of Project - Fields screen

Summary

Slow execution of Project - Fields are caused by large number configuration items (custom-fields) present in the view. JIRA inefficiently computes number of related screens and doesn't cache some custom-fields data to optimize the performance.

Environment

Large JIRA instance

• with many custom-fields (1000+)
• with many fieldlayouts items:

  SQL> select count(1) from fieldlayout; 
  556 
  
  SQL> select count(1) from fieldlayoutitem; 
  1355666
  
  SQL> select count(1) from fieldlayoutscheme;  
  477
  

• with many screens

  select count(1) from fieldscreen;
    1322
  
  select count(1) from fieldscreentab;
    1803
  

Steps to Reproduce

1. Create large JIRA instance 
2. Open Project configuration screen
3. Go to Fields Scheme (<JIRA>/plugins/servlet/project-config/<PRG>/fields)

Expected Results

Initial load time is low (< 30 seconds) time and second execution is fast (< 10 seconds).

Actual Results

Call takes 80-90 seconds, second call still slow.

Notes

From SQL log you can see a lot of calls to DB. Total number of executed SQLs 11850:

// Break down by table
awk -F'(FROM|WHERE)' '/SELECT/{print $2}' http-bio-8443-exec-11_sql | sort | uniq -c | sort -k1,1 -n

 363  fieldlayoutschemeentity
 393  fieldlayout
 393  fieldlayoutitem
 504  nodeassociation
 853  fieldlayoutscheme
1121  fieldscreen
1121  fieldscreentab
1318  propertyentry
1541  fieldscreenlayoutitem
4240  managedconfigurationitem

Second call still does a lot of SQL call to managedconfigurationitem table, this data is not cashed:

grep 'http-bio-8080-exec-8' atlassian-jira-sql.log.3 | awk -F'(FROM|WHERE)' '/SELECT/{print $2}' | sort | uniq -c | sort -k1,1 -n
   1  public.fieldlayoutscheme ORDER BY NAME ASC"
   1  public.userhistoryitem
   2  public.propertyentry
   5  public.kpluginscfg
4466  public.managedconfigurationitem

Profile data

Workaround

This is performance problem in JIRA. You can lower the impact by reducing the number of items (custom-fields) for Field Configuration for project, to do that, please Hide the field.