Uploaded image for project: 'Kuali Rice Development'
  1. Kuali Rice Development
  2. KULRICE-8847

Role lookups slow when there are over 200 roles

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.1.2
    • Fix Version/s: 2.1.4, 2.2.2
    • Component/s: Development, Performance
    • Security Level: Public (Public: Anyone can view)
    • Labels:
      None
    • Rice Module:
      KIM
    • KRAD Feature Area:
      Maintenance
    • Application Requirement:
      KC
    • KAI Review Status:
      Not Required
    • KTI Review Status:
      Not Required
    • Include in Release Notes?:
      Yes

      Description

      The role lookup took close to a minute on unbounded searches. On analyzing this, the longest time taken (99%) was in the RoleDaoOjb class, getRoles method when it tries to get the RoleBos from the db in line

      Query q = QueryFactory.newQuery(RoleBo.class, criteria);

      This again is probably because of loading role members for each of the roles in the database. Don't think RoleBoLite would help because the associated members are required in order to do role lookups on principal name. Perhaps one of the ways to improve performance would be to check if a principal name field is specified and if not use RoleBoLite instead of RoleBo? I do not think the membership information is otherwise needed in the search results. Other than this, direct SQL might be needed like in KULRICE-8415

      Attaching simulated dataset for testing, please let me know if a different format is needed. The current format is for MySQL and can be added to any Rice db with command "source fileName" after choosing the db first.

        Attachments

        1. _KimOjbSpringBeans_xml.patch
          0.8 kB
        2. KULRICE.sql.gz
          4.20 MB
        3. KULRICE-8847.oracle.sql.gz
          4.51 MB
        4. RoleDaoOjb.java
          46 kB
        5. RoleDaoOjb.patch
          36 kB
        6. RoleDaoOjb2.patch
          36 kB
        7. RoleLookup1.png
          RoleLookup1.png
          92 kB
        8. RoleLookup2.png
          RoleLookup2.png
          90 kB

          Issue Links

            Activity

            Hide
            sonam Sona Sona (Inactive) added a comment - - edited

            Hi Gayathri, I have already committed changes in however I was not able to confirm the null pointer exception. Please take a look at the screen shots attached. I did a search for

            *aggregat* 

            and

            *creat*

            and both seem to work fine for me locally. I am working against the latest 2.1 branch code. Thanks.

            Show
            sonam Sona Sona (Inactive) added a comment - - edited Hi Gayathri, I have already committed changes in however I was not able to confirm the null pointer exception. Please take a look at the screen shots attached. I did a search for *aggregat* and *creat* and both seem to work fine for me locally. I am working against the latest 2.1 branch code. Thanks.
            Hide
            gathreya Gayathri Athreya added a comment -

            OK, if you have committed the code, I will get the latest and try again. Thanks Sona.

            Show
            gathreya Gayathri Athreya added a comment - OK, if you have committed the code, I will get the latest and try again. Thanks Sona.
            Hide
            gathreya Gayathri Athreya added a comment - - edited

            Hello, it looks like this improvement may not be enough When the number of entries in the role_members table increases as a result of the increase in role_member_attribute data, the application response time is extremely slow. For example, I created a dataset that returned 3017207 rows for this query
            select count(*)
            FROM KRIM_ROLE_MBR_T A0
            JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO
            ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID
            JOIN KRIM_ATTR_DEFN_T C0
            ON BO.KIM_ATTR_DEFN_ID = C0.KIM_ATTR_DEFN_ID;

            and Geo's data at MIT returned
            233354
            for the same query and in both cases, the application took over 15 minutes to process the request. Unfortunately, I think this role_member data is only going to keep increasing as more and more documents are created since our attribute data is the proposal/protocol number in some cases. Using RoleBoLite would definitely improve this situation but the role member information is required for the "principal name" lookup search, is it not? And the role_mbr_attr_data is also required to discern the type of role? Also, though only about 100 results are displayed per page, it looks like all of the roles are processed at once? Is it possible to limit the number of roles processed per page?

            Show
            gathreya Gayathri Athreya added a comment - - edited Hello, it looks like this improvement may not be enough When the number of entries in the role_members table increases as a result of the increase in role_member_attribute data, the application response time is extremely slow. For example, I created a dataset that returned 3017207 rows for this query select count(*) FROM KRIM_ROLE_MBR_T A0 JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID JOIN KRIM_ATTR_DEFN_T C0 ON BO.KIM_ATTR_DEFN_ID = C0.KIM_ATTR_DEFN_ID; and Geo's data at MIT returned 233354 for the same query and in both cases, the application took over 15 minutes to process the request. Unfortunately, I think this role_member data is only going to keep increasing as more and more documents are created since our attribute data is the proposal/protocol number in some cases. Using RoleBoLite would definitely improve this situation but the role member information is required for the "principal name" lookup search, is it not? And the role_mbr_attr_data is also required to discern the type of role? Also, though only about 100 results are displayed per page, it looks like all of the roles are processed at once? Is it possible to limit the number of roles processed per page?
            Hide
            gthomas Geo Thomas (Inactive) added a comment -

            I tested rice version 2.1.4-r37679 with MIT data. The unbounded Role BO lookup came up in less than a second. Earlier it used to take more than 15 minutes. Huge improvement!!!!

            Gayathri, We can definitely resolve this.

            Show
            gthomas Geo Thomas (Inactive) added a comment - I tested rice version 2.1.4-r37679 with MIT data. The unbounded Role BO lookup came up in less than a second. Earlier it used to take more than 15 minutes. Huge improvement!!!! Gayathri, We can definitely resolve this.
            Hide
            gathreya Gayathri Athreya added a comment - - edited

            Awesome, thanks Peter and Sona I will close these jiras once I have had a chance to update our KC instances with the updated Rice versions. Thanks.

            Show
            gathreya Gayathri Athreya added a comment - - edited Awesome, thanks Peter and Sona I will close these jiras once I have had a chance to update our KC instances with the updated Rice versions. Thanks.

              People

              • Assignee:
                sonam Sona Sona (Inactive)
                Reporter:
                gathreya Gayathri Athreya
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: