[KULRICE-8849] removePrincipalFromRole and getRoleMemberPrincipalId methods very slow Created: 28/Jan/13  Updated: 21/Jan/14  Resolved: 12/Feb/13

Status: Closed
Project: Kuali Rice Development
Component/s: Development, Performance
Affects Version/s: None
Fix Version/s: 2.1.3
Security Level: Public (Public: Anyone can view)

Type: Improvement Priority: Critical
Reporter: Gayathri Athreya Assignee: Sona Sona (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File KCINFR-713-oracle.sql.gz     File KCINFR-713.sql.gz     Java Source File ProposalPermissionsAddUserToRoleTest.java     Java Source File RoleDaoOjb.java     File _KimOjbSpringBeans_xml.patch    
Issue Links:
relates to KULRICE-8836 Person maintenance screen loading ver... Closed
is related to KULRICE-8959 Investigate if column indices can be ... Closed
Rice Module:
Application Requirement:
KAI Review Status: Not Required
KTI Review Status: Not Required


removePrincipalFromRole and getRoleMemberPrincipalId methods perform poorly when there are about 85,000 members assigned to the role. Attaching simulated datasets for testing but this requires a KC application running because of the KIM_TYP_IDs and the attribute definition used in the dataset. Please let me know if you have trouble loading the data, a MySQL "source fileName" should do it. If you need a different format, please let me know.

Using the test data set, a unit test to remove principals from a role with 85000 members took 11 seconds and the method getRoleMemberPrincipalId took about 1 minute. Higher execution times were observed while using actual data. Please look at the associated KCINFR-713 jira for the issue reported at IU and Eric's comment on the fix. The method assignPrincipalToRole may also require a similar fix since that is a potential place for performance enhancements also. Most of these issues with Role performance seem to be related to the RoleBo class which loads all the role members for the role. In some places, RoleBoLite has been used to improve performance but since these methods require the membership information, it might not be an option.

Comment by Sona Sona (Inactive) [ 05/Feb/13 ]

Hi Gayathri,
I got KC running locally but I have it setup with Oracle as the db. I wanted to check with you if you could get me the dataset as an oracle dump? Thanks.

Comment by Gayathri Athreya [ 05/Feb/13 ]

Hi Sona,
Can I get you a .sql file so you can do a @fileName.sql ? Or CSV? I can modify my script to get you whatever format you want, just let me know.

Comment by Gayathri Athreya [ 05/Feb/13 ]

Hi, I've attached the oracle version that you should be able to import using @KCINFR-713-oracle.sql . I could not test it since I run MySQL so let me know if you find errors.

Comment by Sona Sona (Inactive) [ 12/Feb/13 ]

Added fixes for getRoleMemberPrincipalId and the time is down to ~10 sec from 60 sec. Also added a fix for removePrincipalFromRole and assignPrincipalToRole and the time is down to ms. There is one more thing that can be done is to look at the actual query that is being fired and get some column indices set up if we can. This involves setting up P6Spy with KC which is not working for me right now. So, I am resolving this issue and creating another jira to research if some column indices can be added.

Comment by Gayathri Athreya [ 26/Feb/13 ]

Do I need any other patches to test this than the ones I used for KULRICE-8847?

Comment by Sona Sona (Inactive) [ 26/Feb/13 ]

These changes have already been committed.

Comment by Gayathri Athreya [ 26/Feb/13 ]

So I did test this and these are the times I noted

testRemovePrincipalFromRole - 5609 ms
testGetRoleMemberPrincipalIds - 10530 ms
testAssignPrincipalToRole - 6771 ms

I definitely see a performance improvement but will have to wait for the testers to determine if this improves speed for them with real data. Thanks.

Comment by Geo Thomas (Inactive) [ 26/Feb/13 ]

Is it possible to get a patch for this and apply to my local rice? I will give it a try with MIT data.

Comment by Gayathri Athreya [ 26/Feb/13 ]

Yes, I am attaching the new RoleDaoOjb class and an associated patch. Use the springBeans xml patch and replace your roleDaoOjb with this one. Geo, this should speed up unbounded RoleLookups also BTW. Please test that too.

Comment by Geo Thomas (Inactive) [ 14/Mar/13 ]

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!!!!
I also tested PD Permission tab and Person update with different set of permissions. All looked good.
Gayathri, We can resolve this.

Comment by Gayathri Athreya [ 14/Mar/13 ]

Awesome, thanks Sona

Generated at Wed Aug 12 21:16:17 CDT 2020 using JIRA 7.0.11#70121-sha1:19d24976997c1d95f06f3e327e087be0b71f28d4.