Details
-
Bug
-
Resolution: Done
-
Medium
-
1.2, 1.2.5
Description
We have a performance problem around the loading of the "request type list" on the customer portal.
If you have a slow (-ish) database and say > 50 request types defined then it can take too long to render.
This is caused because the requet type loading code is too eager. It loads all the request types for a portal AND the form and fields and field values for that request type. Its doing this in a loop per request type.
[INFO] [talledLocalContainer] 2014-05-06 09:36:29,603 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT f."ID" AS "AO_54307E_GROUP__aointernal__id",t."ID" AS "AO_54307E_GROUPTOREQUESTTYPE__aointernal__id",f."VIEWPORT_ID",f."GROUP_NAME" FROM public."AO_54307E_GROUPTOREQUESTTYPE" t INNER JOIN public."AO_54307E_GROUP" f ON t."GROUP_ID" = f."ID" WHERE t."REQUEST_TYPE_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:29,627 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT * FROM public."AO_54307E_VIEWPORTFIELD" WHERE "FORM_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:29,742 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT * FROM public."AO_54307E_VIEWPORTFIELDVALUE" WHERE "FIELD_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:29,764 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT * FROM public."AO_54307E_VIEWPORTFIELDVALUE" WHERE "FIELD_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:29,785 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT * FROM public."AO_54307E_VIEWPORTFIELDVALUE" WHERE "FIELD_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:29,866 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT f."ID" AS "AO_54307E_GROUP__aointernal__id",t."ID" AS "AO_54307E_GROUPTOREQUESTTYPE__aointernal__id",f."VIEWPORT_ID",f."GROUP_NAME" FROM public."AO_54307E_GROUPTOREQUESTTYPE" t INNER JOIN public."AO_54307E_GROUP" f ON t."GROUP_ID" = f."ID" WHERE t."REQUEST_TYPE_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:29,902 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT * FROM public."AO_54307E_VIEWPORTFIELD" WHERE "FORM_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:30,045 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT * FROM public."AO_54307E_VIEWPORTFIELDVALUE" WHERE "FIELD_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:30,066 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT * FROM public."AO_54307E_VIEWPORTFIELDVALUE" WHERE "FIELD_ID" = ? [INFO] [talledLocalContainer] 2014-05-06 09:36:30,086 http-bio-2990-exec-13 DEBUG admin 574x14649x1 1xd7qel 0:0:0:0:0:0:0:1 /servicedesk/customer/portal/2/group/7 [net.java.ao.sql] SELECT * FROM public."AO_54307E_VIEWPORTFIELDVALUE" WHERE "FIELD_ID" = ?
We need to improve the access path so that we only loads the request type high level details so that "request type list" page remains snappy.
Workaround:
- Dramatically reduce the number of the request type < 30
- Put the request type into category so it does not load at the same time.