performance improvements

There are some performance problems in 1.4 UI:

> ----Original Message----
> From: Chris Hyzer
> Sent: Sunday, March 29, 2009 1:49 AM
> To: grouper-dev@internet2.edu
> Subject: grouper 1.4 performance
>
> Hey,
>
> I propose we take a little bit of time to address performance issues in
> 1.4. I have a few at Penn, and we don't have a huge grouper
> deployment, so perhaps we could reach out to users and see if anyone
> else is having performance issues and see if there is some low hanging
> fruit. In short, I would like to do some simple things to make Grouper
> scale to large deployments.
>
> My definition of a performance problem is if an operation doesn't work,
> or if it takes more than 10 seconds for a user operation (general
> usability guideline). Also if there are N queries/rows going on for an
> operation, and it could be 1 or log(N) or N/100, that is a problem.
>
> Here is my proposal to address Penn's issues (below). Note, we are
> having issues with the UI, so that is what I am concerned about. The
> strategies could easily be exposed to WS in 1.4 as well, especially if
> people need it (well, the Stem.getChildGroups and composite stuff will
> automatically be there, but the member sorting/paging is not
> automatic).
>
> Thanks,
> Chris
>
> Ps. No need to look too closely to the code, unless you are in to that
> sort of thing...
>
> #############################################
> 1. We have a group of facultyStudentStaff with 60k members, and it is
> not publicly viewable. So if someone wants to make a "requireGroup"
> with it (intersection), we need to grant View on it to them. When we
> do, all members are listed, and the screen never draws in the UI. Gary
> mentioned not fetching members where the size is too large, but I think
> it would be better if we could use paging. I added paging into the
> Grouper Hibernate API, so the query would look like this:
>
> (From HibernateSessionTest):
>
> i2.addMember(SubjectTestHelper.SUBJ0);
> i2.addMember(SubjectTestHelper.SUBJ1);
> i2.addMember(SubjectTestHelper.SUBJ2);
> i2.addMember(SubjectFinder.findAllSubject());
> i2.addMember(SubjectFinder.findRootSubject());
>
> //this means 3 rows per page, first pagel, of course in reality the
> page size would be e.g. ~50
> QueryPaging queryPaging = QueryPaging.page(3, 1, true);
>
> //page the members in a group
> List<Member> members = HibernateSession.byHqlStatic()
> .createQuery("select distinct theMember from Member as theMember,
> Membership as theMembership, Field theField "
> + "where theMembership.ownerUuid = :ownerId and theMember.uuid =
> theMembership.memberUuid" +
> " and theMembership.fieldId = theField.uuid and
> theField.typeString = 'list' and theField.name = 'members'")
> .setString("ownerId", i2.getUuid())
>
> .sort(QuerySort.asc("theMember.subjectIdDb")).paging(queryPaging).list(
> Member.class);
>
> //here are the first three subjects, ordered by subjectId
> assertEquals("GrouperAll, GrouperSystem, test.subject.0",
> Member.subjectIds(members));
>
> //note, it figured out the total count for us, of 5
> assertEquals(5, queryPaging.getTotalRecordCount());
>
> //total of 5, at 3 per page, means 2 pages. It figured this out
> too.
> assertEquals(2, queryPaging.getNumberOfPages());
>
> So basically I think we overload some of the group.getMembers() methods
> (all, immediate, effective) to take a QueryOptions bean which can have
> paging and/or sorting in it. The results would not be sorted by the
> display string until we add more cols to grouper_members, which would
> not be until at least Grouper 1.5. But I think it is a step in the
> right direction.
>
> #####################################################
> 2. Clicking on a folder with 1000 groups
>
> I think we have been discussing going in the direction with privilege
> management where we can have our interface define more methods so that
> we can do things efficiently if storing privileges in Grouper, but it
> is still possible (though perhaps less performant) to have external
> privileges. I would like to take this one step better where we provide
> a base class implementation of the privilege interfaces so that there
> are very few mandatory methods to implement (like now), and there are
> default high level overridable operation implementations which use the
> low level privilege operations. Basically this still makes it easy to
> do external privs. Anyways, for the internal one, cant we do group
> operations with privs all in one query (granted if the grouperSession
> is admin, the query will be different, so we need two queries for each
> operation. Here would be the query for a non-admin listing a certain
> number of groups from a parent stem. Again, in 1.4 the groups would
> not be sorted (well, sorted by uuid which is like not being sorted),
> though in 1.5 since we moved the name/displayName/etc to the groups
> table, we can easily sort.
>
> i2.grantPriv(SubjectTestHelper.SUBJ1, AccessPrivilege.READ, false);
> i3.revokePriv(SubjectFinder.findAllSubject(), AccessPrivilege.READ,
> false);
> i3.revokePriv(SubjectFinder.findAllSubject(), AccessPrivilege.VIEW,
> false);
> i4.grantPriv(SubjectTestHelper.SUBJ1, AccessPrivilege.READ, false);
> i5.grantPriv(SubjectTestHelper.SUBJ1, AccessPrivilege.READ, false);
> i6.grantPriv(SubjectTestHelper.SUBJ1, AccessPrivilege.READ, false);
> i7.grantPriv(SubjectFinder.findAllSubject(), AccessPrivilege.READ,
> false);
>
> List<String> uuids = GrouperUtil.toList(i2.getUuid(), i4.getUuid(),
> i5.getUuid(), i6.getUuid(), i7.getUuid());
>
> Collections.sort(uuids);
>
> queryPaging = QueryPaging.page(3, 1, true);
> QuerySort querySort = QuerySort.asc("g.uuid");
>
> List<Group> groups = HibernateSession.byHqlStatic()
> .createQuery("select distinct g from Group as g, Membership as m,
> Field as f, Attribute as a " +
> "where a.groupUuid = g.uuid and g.parentUuid = :parent " +
> "and m.ownerUuid = g.uuid and m.fieldId = f.uuid and
> f.typeString = 'access' " +
> "and (m.memberUuid = :sessionMemberId or m.memberUuid =
> :grouperAllUuid)")
> .setString("parent", edu.getUuid())
> .setString("sessionMemberId",
> MemberFinder.findBySubject(grouperSession,
> SubjectTestHelper.SUBJ1).getUuid())
> .setString("grouperAllUuid",
> MemberFinder.findBySubject(grouperSession,
> SubjectFinder.findAllSubject()).getUuid())
> .paging(queryPaging).sort(querySort)
> .list(Group.class);
>
> assertEquals(3, groups.size());
> assertEquals(5, queryPaging.getTotalRecordCount());
> assertEquals(uuids.get(0), groups.get(0).getUuid());
> assertEquals(uuids.get(1), groups.get(1).getUuid());
> assertEquals(uuids.get(2), groups.get(2).getUuid());
>
> #########################################
> 3. making a composite (intersection or complement) of a small group and
> a large group. We have a use case where we like to intersect with
> allEmployees or facultyStudentStaff, etc, and it takes a LONG time to
> figure out a few members (since it selects all from each group)... I
> think we can do this all in one query. Also, right now all columns in
> the membership object are returned, then only the ID is used, so we
> only need to select the ID. Here is an example of an intersection,
> complement, and union in one query each (again, proof of concepts in
> the HibernateSessionTest class):
>
> //intersection in one query
> List<String> memberUuids = HibernateSession.byHqlStatic()
> .createQuery("select distinct theMember.uuid from Member
> theMember, " +
> "Membership theMembership, Membership theMembership2,
> Field theField " +
> "where theMembership.ownerUuid = :group1uuid and
> theMembership2.ownerUuid = :group2uuid " +
> "and theMember.uuid = theMembership.memberUuid and
> theMember.uuid = theMembership2.memberUuid " +
> "and theMembership.fieldId = theField.uuid and
> theMembership2.fieldId = theField.uuid " +
> "and theField.typeString = 'list' and theField.name =
> 'members'")
> .setString("group1uuid", i2.getUuid())
> .setString("group2uuid", i3.getUuid())
> .list(String.class);
>
> assertEquals(1, memberUuids.size());
> assertEquals(MemberFinder.findBySubject(grouperSession,
> SubjectTestHelper.SUBJ1).getUuid(), memberUuids.get(0));
>
> //complement in one query
> memberUuids = HibernateSession.byHqlStatic()
> .createQuery("select distinct theMember.uuid from Member theMember,
> " +
> "Membership theMembership, Field theField " +
> "where theMembership.ownerUuid = :group1uuid " +
> "and theMember.uuid = theMembership.memberUuid " +
> "and theMembership.fieldId = theField.uuid " +
> "and theField.typeString = 'list' and theField.name = 'members'
> " +
> "and not exists (select theMembership2.memberUuid from
> Membership theMembership2 " +
> "where theMembership2.memberUuid = theMember.uuid and
> theMembership.fieldId = theField.uuid " +
> "and theMembership2.ownerUuid = :group2uuid) ")
> .setString("group1uuid", i3.getUuid())
> .setString("group2uuid", i2.getUuid())
> .list(String.class);
>
> assertEquals(1, memberUuids.size());
> assertEquals(MemberFinder.findBySubject(grouperSession,
> SubjectTestHelper.SUBJ4).getUuid(), memberUuids.get(0));
>
> //union in one query (granted, this is less of an issue since you
> need all data from both,
> //but might as well be consistent)
> memberUuids = HibernateSession.byHqlStatic()
> .createQuery("select distinct theMember.uuid from Member theMember,
> " +
> "Membership theMembership, Membership theMembership2, Field
> theField " +
> "where theMembership.ownerUuid = :group1uuid and
> theMembership2.ownerUuid = :group2uuid " +
> "and (theMember.uuid = theMembership.memberUuid or
> theMember.uuid = theMembership2.memberUuid) " +
> "and theMembership.fieldId = theField.uuid and
> theMembership2.fieldId = theField.uuid " +
> "and theField.typeString = 'list' and theField.name =
> 'members'")
> .setString("group1uuid", i2.getUuid())
> .setString("group2uuid", i3.getUuid())
> .list(String.class);
>
> assertEquals(6, memberUuids.size());