Grouper Developers Forum

[Shilen Patel <>]

Tom Barton wrote:
> Shilen Patel wrote:
>
> >>> 1.  Navigating through the stems viewable in the "My Memberships" 
> >>> section in the UI takes about 2-3 seconds per page.
> >> This seems slow. how many groups are average individuals a member of?
> > Usually around 2-5 for employees and 10-20 for students.
> > We've made some modifications to the indexes that appear to 
> > significantly help on this.  I'm going to test these changes on a new 
> > database server so I can verify the problem was not caused by our old 
> > database server.
>
> To be sure we're on the same page, would you please indicate which 
> version of grouper (api, ui) you're running, which jvm, which rdbms, 
> and for the ui, on which tomcat/apache. Also please list any changes 
> to the vanilla DDL that you've made.
>
> Thanks!
> Tom

We're using Grouper 1.2.0-rc4 with the following:

Java 1.5.0_10
Oracle 10.2.0.3
Apache Tomcat 5.5.9
Apache 2.0.52


We noticed problems with the following two queries:

select hibernatem0_.id as id, hibernatem0_.owner_id as owner_id, 
hibernatem0_.member_id as member_id, hibernatem0_.list_name as 
list_name, hibernatem0_.list_type as list_type, hibernatem0_.mship_type 
as mship_type, hibernatem0_.via_id as via_id, hibernatem0_.depth as 
depth, hibernatem0_.parent_membership as parent_m9_, 
hibernatem0_.membership_uuid as members10_, hibernatem0_.creator_id as 
creator_id, hibernatem0_.create_time as create_12_ from 
grouper_memberships hibernatem0_ where (hibernatem0_.owner_id=? 
)and(hibernatem0_.list_name=? )and(hibernatem0_.list_type=? 
)and(hibernatem0_.mship_type=? )

select hibernatem0_.id as id, hibernatem0_.owner_id as owner_id, 
hibernatem0_.member_id as member_id, hibernatem0_.list_name as 
list_name, hibernatem0_.list_type as list_type, hibernatem0_.mship_type 
as mship_type, hibernatem0_.via_id as via_id, hibernatem0_.depth as 
depth, hibernatem0_.parent_membership as parent_m9_, 
hibernatem0_.membership_uuid as members10_, hibernatem0_.creator_id as 
creator_id, hibernatem0_.create_time as create_12_ from 
grouper_memberships hibernatem0_ where (hibernatem0_.member_id=? 
)and(hibernatem0_.list_name=? )and(hibernatem0_.list_type=? )


So we tried the following.

drop index MEMBERSHIP_FIELD_IDX;
drop index MEMBERSHIP_OWNER_IDX;
drop index MEMBERSHIP_TYPE_IDX;

CREATE INDEX IMS.GROUPER_MEMBERSHIPS_IDX1 ON IMS.GROUPER_MEMBERSHIPS
(OWNER_ID, LIST_NAME, LIST_TYPE, MSHIP_TYPE);
CREATE INDEX IMS.GROUPER_MEMBERSHIPS_IDX2 ON IMS.GROUPER_MEMBERSHIPS
(MEMBER_ID, LIST_NAME, LIST_TYPE);


I've tried these index changes on two database servers and have seen 
similar results on both.

The time to delete a group drops from 2.18 seconds to .18 seconds. 
The time to delete a stem drops from .40 seconds to .08 seconds.
The time to run Member.getMemberships() for a person with 7 memberships 
drops from .55 seconds to .10 seconds.

The performance of the UI in the My Memberships section is also 
significantly better as I mentioned in my last email.

I haven't seen any significant negative affects of these changes, but I 
also don't know all the possible queries the API can make.

Thanks,

-- Shilen