Kuali Rice Development
  1. Kuali Rice Development
  2. KULRICE-7690

Date Approved Workflow Preference can cause an excessive amount of sql queries

    Details

    • Type: Improvement Improvement
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.4
    • Fix Version/s: 2.1.1
    • Component/s: Development
    • Security Level: Public (Public: Anyone can view)
    • Labels:
      None
    • Similar issues:
      KULRICE-7714Performance improvements in regards to workflow data access
      KULRICE-3774Simple Document Search queries do not take proper advantage of indexes on "create date" in (at least) Oracle
      KULRICE-3702Reduce excessive KEW-based KIM calls (blanket approve and admin routing)
      KULRICE-8218Emails can be sent to secondary approvers even if they should not be based on preferences
      KULRICE-12406Add method to get privacy preference information to IdentityService
      KULRICE-6846null Lookup criteria causes sql exception
      KULRICE-3914Materializing JPA/Hibernate queries causes strange exception
      KULRICE-7094DocumentSearchGeneratorImpl creates bad SQL when UI date format is changed
      KULRICE-14269JPA predicates cause unnecessary SQL joins and performance degradation
      KULRICE-8105RouteNodeDAOOjbImpl.getInitialNodeInstances query performs unfavorably on MySQL
    • Rice Module:
      KEW
    • KAI Review Status:
      Not Required
    • KTI Review Status:
      Not Required

      Description

      We had a scenario where several users had around 500 items in there action list. Many of these items had moderate workflow complexity (several nodes, a branch, etc.). Also these items were quite far into the approval chain. The users has the Date Approved Workflow Preference enabled. When the user tried to click on the action list the request was timing out. Here is the cause of that timeout and what can be done to fix it. The following is based on analysis I did on my local machine.

      When the user clicked the action list, 67,381 select queries were generated. This was causing the request to take over 9 minutes. Based on our server settings, this would normally exceed the timeout limit. On a side note, if the request did succeed, the action list is cached in user session so subsequent requests would be much quicker.

      The troublesome code is the following:

      ActionItemActionListExtension.initialize(Preferences preferences) specifically the following block:

              if (KEWConstants.PREFERENCES_YES_VAL.equals(preferences.getShowDateApproved())) {
                  setLastApprovedDate(KEWServiceLocator.getActionTakenService().getLastApprovedDate(getRouteHeaderId()));
              }
      

      This code ends up being called for each item in the action list. The getLastApprovedDate method calls into a DAO that retrieves a collection of ActionTakenValue. The service ONLY cares about the Action Date of the ActionTakenValue. Unfortunately due to the OJB configuration, OJB eagerly retrieves a lot of other objects that are related to the ActionTakenValue. By making a one line change to the ojb configuration I was able to bring the number of queries down to 11,627 which makes a total of 55,754 less queries. I'll attach a patch with the change.

      Basically the ActionTakenValue was eagerly retrieving a collection of ActionRequestValue without proxying. Each ActionRequestValue was eagerly retrieving many other things including a RouteNodeInstance. The RouteNodeInstance causes a lot of queries to be executed due to it's kew configuration.

      The fix was to change the ojb configuration for ActionTakenValue to make sure the collection of ActionRequestValue was using a proxied collection.

      1. patch.txt
        0.9 kB
        Travis Schneeberger

        Issue Links

          Activity

          Hide
          Travis Schneeberger added a comment - - edited

          Even with this optimization, 11,627 queries for one request is too many. The majority of these remaining queries are coming from the following code in the ActionList.jsp

          <c:if test="${preferences.showCurrentNode == Constants.PREFERENCES_YES_VAL}">
            <display-el:column sortable="true"
              title="${currentRouteNodesLabel}"
              sortProperty="routeHeader.currentRouteLevelName" class="infocell">
                <c:out value="${result.routeHeader.currentRouteLevelName}" />&nbsp;
            </display-el:column>
          </c:if>
          

          This is related to the Current Route Node(s) option in User Preferences. Removing this block from the jsp reduced the amount of queries by 6,543. I'll create another related jira once I figure out an easy way to optimize this.

          Show
          Travis Schneeberger added a comment - - edited Even with this optimization, 11,627 queries for one request is too many. The majority of these remaining queries are coming from the following code in the ActionList.jsp <c: if test= "${preferences.showCurrentNode == Constants.PREFERENCES_YES_VAL}" > <display-el:column sortable= " true " title= "${currentRouteNodesLabel}" sortProperty= "routeHeader.currentRouteLevelName" class= "infocell" > <c:out value= "${result.routeHeader.currentRouteLevelName}" />&nbsp; </display-el:column> </c: if > This is related to the Current Route Node(s) option in User Preferences. Removing this block from the jsp reduced the amount of queries by 6,543. I'll create another related jira once I figure out an easy way to optimize this.
          Hide
          Travis Schneeberger added a comment -

          According to Eric both the Current Route Node(s) and Date Approved problems are fixed in rice 2.1.1. We may consider backporting these fixes for rice 1.x

          See

          RouteNodeDAOOjbImpl.getCurrentRouteNodeNames

          and

          ActionTakenDAOOjbImpl.getLastActionTakenDate

          Show
          Travis Schneeberger added a comment - According to Eric both the Current Route Node(s) and Date Approved problems are fixed in rice 2.1.1. We may consider backporting these fixes for rice 1.x See RouteNodeDAOOjbImpl.getCurrentRouteNodeNames and ActionTakenDAOOjbImpl.getLastActionTakenDate
          Hide
          Travis Schneeberger added a comment -

          KULRICE-7714 is a better solution to this problem.

          Show
          Travis Schneeberger added a comment - KULRICE-7714 is a better solution to this problem.
          Hide
          Jessica Coltrin (Inactive) added a comment -

          Closing this since the comments show it's fixed in 2.1.1 and KC is now on 2.3.x.

          Show
          Jessica Coltrin (Inactive) added a comment - Closing this since the comments show it's fixed in 2.1.1 and KC is now on 2.3.x.

            People

            • Assignee:
              Unassigned
              Reporter:
              Travis Schneeberger
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Structure Helper Panel