Grouper Developers Forum

[Chris Hyzer <>]

I took a pass at implementing the sorting/paging for listing large groups 
(e.g. when assigning privileges or browsing memberships).

When I tried to sort by subjectId and page with mysql, it was slow (3 minutes 
for a 40k group).  But if I page without sorting, it is very fast.  I wonder 
how oracle would do.  Anyways, the concept of the sort limit will still be 
needed (though maybe up to 10k once we get everything working in 1.5).

I made a tag to where we were before, since this was a lot of changes: 
GROUPER_1_4_20090407

Gary, if you are still here, can you review the changes and test the UI?  Or 
anyone can test the API/UI if you don't mind so we can make sure the 1.4 
branch is still stable.  I shouldn't have affected existing API methods, 
though I will run the unit tests...

Attached is a (not very interesting) screenshot.

Here is an example of the API.  Here is getting the resultSize of an 
operation (immediate members), without listing all the members:

    QueryOptions queryOptions = new 
QueryOptions().retrieveCount(true).retrieveResults(false);
    group.getImmediateMembers(field, queryOptions);
    int totalSize = queryOptions.getCount().intValue();


Here is a paged query:

      QueryPaging queryPaging = new QueryPaging();
      queryPaging.setPageSize(pageSize);
      queryPaging.setFirstIndexOnPage(start);

      //.sortAsc("m.subjectIdDb")   this kills performance
      queryOptions = new QueryOptions().paging(queryPaging);

      Set<Member> members = group.getImmediateMembers(field, queryOptions);

Here is the HibernateSession underlying API (just pass the queryOptions 
along):

      return HibernateSession.byHqlStatic().options(queryOptions)
        .createQuery(
            query)
        .setCacheable(false)
        .setCacheRegion(KLASS + ".FindAllMembersByOwnerAndField")
        .setString( "owner", ownerUUID ) 
        .setString( "fieldId", f.getUuid() )
        .setString( "type", type )
        .listSet(Member.class);

For sorting, you just add that to the query options, but you have to know the 
hibernate hql sort field [we should document these in the queries and not 
change :) ]


      queryOptions = new 
QueryOptions().paging(queryPaging).sortAsc("m.subjectIdDb");

      Set<Member> members = group.getImmediateMembers(field, queryOptions);

Let me know how it does,
Thanks,
Chris


> -----Original Message-----
> From: Chris Hyzer
> Sent: Monday, March 30, 2009 10:51 AM
> To: 'GW Brown, Information Systems and Computing'; grouper-
> 
> Subject: RE: [grouper-dev] grouper 1.4 performance
> 
> 
> 
> > This can work - the UI has a notion of paging in some areas - but
> > should still only kick in when we are over the configured limit for
> > sorting. I look forward to having sorting through the API in 1.5, but
> > sorting is useful in most cases so we shouldn't lose it in 1.4.
> 
> Agreed.  If the display limit is 200, and the sort limit is 50, the
> easiest thing is probably just to get the first page of 200.  Then if
> less than 200, sort it, and show 50.  If greater than 200, just show
> 50.  Alternatively, we can get the full count, and based on that either
> get up to 200 and sort, or just 50.  They are probably the same
> performance, so whatever is easiest to code in 1.4
> 
> 
> >
> > There is an issue with displaying membership lists which include
> > effective memberships. The UI processes the list of memberships so it
> > can figure out how many 'routes' for a membership there are -
> > see<https://bugs.internet2.edu/jira/browse/GRP-55>
> 
> No problem.  I think once we get the members, we can get a list of
> counts for them in one, or we can get all memberships for only the 50
> members we need in one query.  We can use an "in" clause in 1 query,
> will be easy and good performance.  Again, if you are already working
> with memberships, and that is easiest for 1.4, would be fine for
> performance.
> 
> >
> > Group.getAdmins etc could also be paged - in case anyone adds privs
> to
> > a large group rather than GrouperAll
> 
> Sure.  And we can do the AccessAdapter thing where the implementor
> doesn't have to implement paging, but if they do, it would be better...
> again, in 1.5 if we put more info in the members table, it will be
> sorted.  In 1.4, it will be seemingly unordered (e.g. by subjectId)
> 
> 
> > Depending on the browse mode the UI does different things:
> >
> > MyMemberships: only groups you are a member of which you can VIEW
> > Join groups:   only groups where you have OPTIN
> > Manage groups: only groups where you have UPDATE or ADMIN
> > Explore:       only groups where you have at least VIEW privilege
> >
> > so you would have to join on grouper_fields - or get the ids for the
> > fields and do an 'in' clause - does Hibernate have a way of passing a
> > collection for that or do you have to build dynamic SQL - or have
> separate queries?
> 
> We can easily handle this is a well-coded way.  I actually picture a
> generic method that uses regex where you pass it an HQL, and the
> security you want on it (granted, this is the AccessAdapter doing
> this), and returns the HQL of the secured query.  It would just add
> some tables to the from clause, and some constraints on the where
> clause.  And it can do "in" to pick and choose the privileges.  Then
> that generic method would be used by a bunch of default implementations
> we need (e.g. like the ones above) so it is easy to do easy things.  Or
> it could be in the query options.  E.g. to get all groups of a member
> in a certain stem, paged, sorted (in 1.5+), and with only two
> privileges UPDATE and ADMIN:
> 
> List<Group> groups = member.getGroupsFromParentStem(stem,
>   new QueryOptions().paging(QueryPaging(1,3,true))
>   .sorting(QuerySort.asc("name"))
>   .groupSecurity(GrouperUtil.toSet(AccessPrivilege.UPDATE,
> AccessPrivilege.ADMIN));
> 
> This would under the covers use the AccessAdapter in a smart and
> generic way.
> 
> 
> > >  ...composite stuff...
> > Sounds reasonable.
> 
> 
> Good
> 
> > (from tom)
> > Assuming that users don't typically want to look into the reasons why
> > an effective membership is there, can we optimize things so that that
> data is retrieved only when the user indicates they want to know why?
> 
> Again, for 1.4 I think the least changes the better especially when I
> think we can easily do this efficiently (see above).  But if Gary wants
> to implement this suggestion, it sounds like a good idea.
> 
> 
> > If we are paging I still think we need methods to return the
> > membership count so we can show text such as 'Showing 51-98 of 98
> items ', otherwise a user has no idea how many pages there might be.
> 
> My HibernateSession paging API exposes all this information.  I have a
> paging custom tag I could put into Grouper which has a readonly mode
> and a button mode (both are shown in the attached image, one on top of
> the other... page 7 is being displayed).  If Gary wants to setup the
> action(s) [if the paging bean is in session, I believe I just need to
> pass in the page number and action from custom tag, and it will work] I
> can make the custom tag work.  Gary, is that something you want for 1.4
> or 1.5?
> 
> Thanks!
> Chris
> 
> 
> 
> 
> 
>

Attachment: screen.gif
Description: screen.gif