[JSWCLOUD-6925] Investigate SQL query count executed for a single request

Type: Suggestion
Resolution: Unresolved
Component/s: None
Labels:

UIS:
1
Support reference count:
14
Feedback Policy:

Our product teams collect and evaluate feedback from a number of different sources. To learn more about how we use customer feedback in the planning process, check out our new feature policy.

Switching on sql logging reveals that the custom field ids are queryied over and over again, something which could easily be cached (the id, not the field):

[INFO] [talledLocalContainer] 2012-12-12 11:33:46,146 http-2990-3 DEBUG admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [greenhopper.service.sprint.SprintCustomFieldServiceImpl] Requesting the Default Sprint field
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,146 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 0ms Connection taken. borrowed : 2
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,148 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM PUBLIC.propertyentry WHERE ENTITY_NAME='GreenHopper.properties' AND ENTITY_ID='1' AND PROPERTY_KEY='GreenHopper.Sprint.Default.customfield.id'"
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,148 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 2ms Connection returned. borrowed : 1
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,148 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 0ms Connection taken. borrowed : 2
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,157 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 9ms "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM PUBLIC.propertyentry WHERE ENTITY_NAME='GreenHopper.properties' AND ENTITY_ID='1' AND PROPERTY_KEY='GreenHopper.Sprint.Default.customfield.id'"
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,158 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 10ms Connection returned. borrowed : 1
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,158 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 0ms Connection taken. borrowed : 2
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,159 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms "SELECT propertyvalue FROM PUBLIC.propertynumber WHERE ID='10629'"
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,159 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms Connection returned. borrowed : 1
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,159 http-2990-3 DEBUG admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [greenhopper.service.sprint.SprintCustomFieldServiceImpl] Field ID '10100' has been set -- verifying that the field still exists
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,160 http-2990-3 DEBUG admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [greenhopper.service.sprint.SprintCustomFieldServiceImpl] Default Sprint field with custom field ID '10100' exists
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,160 http-2990-3 DEBUG admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [greenhopper.service.sprint.SprintCustomFieldServiceImpl] Requesting the Default Sprint field
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,160 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 0ms Connection taken. borrowed : 2
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,161 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM PUBLIC.propertyentry WHERE ENTITY_NAME='GreenHopper.properties' AND ENTITY_ID='1' AND PROPERTY_KEY='GreenHopper.Sprint.Default.customfield.id'"
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,161 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms Connection returned. borrowed : 1
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,161 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 0ms Connection taken. borrowed : 2
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,162 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 0ms "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM PUBLIC.propertyentry WHERE ENTITY_NAME='GreenHopper.properties' AND ENTITY_ID='1' AND PROPERTY_KEY='GreenHopper.Sprint.Default.customfield.id'"
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,162 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms Connection returned. borrowed : 1
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,163 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 0ms Connection taken. borrowed : 2
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,164 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms "SELECT propertyvalue FROM PUBLIC.propertynumber WHERE ID='10629'"
[INFO] [talledLocalContainer] 2012-12-12 11:33:46,164 http-2990-3 INFO admin 693x2069x1 1pl6k6w 0:0:0:0:0:0:0:1%0 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [atlassian.jira.ofbiz.LoggingSQLInterceptor] 1ms Connection returned. borrowed : 1

The total number of selects for a single request alone seems to exceed 60

relates to

JRACLOUD-38825 Improve Database Indexes to the PROPERTYENTRY Table

Closed

JSWCLOUD-8775 View on Board operation slow with large amount of boards

Closed

JSWCLOUD-10819 Cache frequently used PropertySets

Closed

Sergey Svishchev added a comment - 09/Jun/2014 11:11 AM

Any news?

Sergey Svishchev added a comment - 09/Jun/2014 11:11 AM Any news?

David Yu added a comment - 08/May/2013 5:52 AM

On a single click to the link View on Board, it generates this many SQL queries...top 4 with the count shown:

    364 "SELECT propertyvalue FROM propertynumber WHERE ID='35795'"
    728 "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM propertyentry WHERE ENTITY_NAME='GreenHopper.properties' AND ENTITY_ID='1' AND PROPERTY_KEY='GreenHopper.EpicStatus.Default.customfield.id'"
    730 "SELECT propertyvalue FROM propertynumber WHERE ID='19787'"
   1461 "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM propertyentry WHERE ENTITY_NAME='GreenHopper.properties' AND ENTITY_ID='1' AND PROPERTY_KEY='GreenHopper.Sprint.Default.customfield.id'"

David Yu added a comment - 08/May/2013 5:52 AM On a single click to the link View on Board , it generates this many SQL queries...top 4 with the count shown: 364 "SELECT propertyvalue FROM propertynumber WHERE ID= '35795' " 728 "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM propertyentry WHERE ENTITY_NAME= 'GreenHopper.properties' AND ENTITY_ID= '1' AND PROPERTY_KEY= 'GreenHopper.EpicStatus.Default.customfield.id' " 730 "SELECT propertyvalue FROM propertynumber WHERE ID= '19787' " 1461 "SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM propertyentry WHERE ENTITY_NAME= 'GreenHopper.properties' AND ENTITY_ID= '1' AND PROPERTY_KEY= 'GreenHopper.Sprint.Default.customfield.id' "

Michael Ruflin added a comment - 12/Dec/2012 12:44 AM

Also, some of our AO managers don't cache, which adds another 15 queries or so (estimation/tracking statistics shows up multiple times)

Michael Ruflin added a comment - 12/Dec/2012 12:44 AM Also, some of our AO managers don't cache, which adds another 15 queries or so (estimation/tracking statistics shows up multiple times)

Jira Cloud

Details

Description

Attachments

Issue Links

Forms

Activity

Collapse comment: Sergey Svishchev added a comment - 09/Jun/2014 11:11 AM

Expand comment: Sergey Svishchev added a comment - 09/Jun/2014 11:11 AM

Collapse comment: David Yu added a comment - 08/May/2013 5:52 AM

Expand comment: David Yu added a comment - 08/May/2013 5:52 AM

Collapse comment: Michael Ruflin added a comment - 12/Dec/2012 12:44 AM

Expand comment: Michael Ruflin added a comment - 12/Dec/2012 12:44 AM

People

Dates