Details

    • Type: Improvement Improvement
    • Status: Closed Closed
    • Priority: Critical 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
    • Similar issues:
      KULRICE-6602PeopleFlow Role lookup not returning
      KULRICE-1967Role - Permission lookup throws 500 server error
      KULRICE-3927Group or Role Lookup fail when a principal does not exist
      KULRICE-4960Role document aborts when we try to edit a role that has a RadioControlDefinition
      KULRICE-6826Role Lookup throws SQLException when using non-role (Permission, Group, Permission Template, Responsibility, Responsibility Template) criteria
      KULRICE-12428On PeopleFlow maint doc, the lookup for a Role member isn't returning the roll
      KULRICE-10687Permission lookup by role name returns error for roles that contain other roles
      KULRICE-12341Performance issues opening large roles
      KULRICE-3236Improve handling of person lookups when role is involved
      KULRICE-8849removePrincipalFromRole and getRoleMemberPrincipalId methods very slow
    • 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.

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

        Issue Links

          Activity

          Hide
          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
          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
          Gayathri Athreya added a comment -

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

          Show
          Gayathri Athreya added a comment - OK, if you have committed the code, I will get the latest and try again. Thanks Sona.
          Hide
          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
          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
          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
          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
          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
          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:
              Sona Sona (Inactive)
              Reporter:
              Gayathri Athreya
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Structure Helper Panel