Grouper Users - Open Discussion List

[Loris Bennett <>]

On Wed, 2009-05-20 at 10:15 -0400, Chris Hyzer wrote:
> What is the performance problem?  The query against the
>  grouper_groups_v?  Or the number of rows returned?  Is it slow only on
>  the first run, or also on subsequent runs (I mention since the loader
>  takes time to insert memberships the first time, and when things don’t
>  change much, it is much quicker)?

The query itself is slow. As far as I can tell it is a sequential scan
over grouper_stems the required to apply the regular expression that is
takes the time. 

> The grouper_groups_v is a little heavy, if that is the bottleneck, you
>  might consider making your own groups view which has only what you
>  need in it.  Granted, when you upgrade grouper you will have to look
>  at it and perhaps adjust (I don’t think this is a big deal, but up to
>  you :) ).

OK, I'll look into this.

Thanks

Loris

> Let me know
> Thanks,
> Chris
> 
> > -----Original Message-----
> > From: Loris Bennett 
> > [mailto:]
> > Sent: Wednesday, May 20, 2009 7:41 AM
> > To: Grouper Users Mailing List
> > Subject: [grouper-users] Efficient loader rollup groups for leaf
> > groups?
> > 
> > Hi,
> > 
> > For an org structure with a an automatically generated group ('%:auto')
> > at each node in the tree we use grouperloader to create roll-up groups
> > of all the auto groups one level below using this slightly modified
> > suggestion Chris made:
> > 
> >         select
> >         gs.name || ':all' as group_name,
> >         ggv.group_id as subject_id,
> >         ggv.name as sub_group_name
> >         from
> >         grouper_stems gs,
> >         grouper_groups_v ggv
> >         where
> >         gs.name like 'sources:employees:'
> >         and
> >         ggv.name similar to gs.name || ':[^:]*:auto'
> >         and
> >         exists (select 1 from grouper_stems gs2 where gs2.name like
> >         gs.name|| ':%')
> > 
> > (actually this turns out to be a bit slow when there are a lot of
> > groups, so we in fact now use a different query which makes use of the
> > fact that grouper_stems contains information about the parent stem of
> > each entry).
> > 
> > I now have a similar, but slightly different problem. In another tree I
> > have the students sorted according to the degree they are studying.
> > That
> > is I have groups like:
> > 
> >         physics:physics:bsc:2008:major:auto
> > 
> > that is :
> > 
> >         department, course name , degree type, version of course,
> >         major/minor
> > 
> > This tree contains auto-groups only in leaf nodes. I want to create
> > roll-up groups which contain all the "auto" groups below them in the
> > hierarchy.
> > 
> > I can solve this using an approach like the one above. I.e. I could
> > select
> > for each stem all the auto groups for which the stem is a substring of
> > the group name. However, this is going to run into similar performance
> > issues to those of the statement above.
> > 
> > Can anyone think of a better approach?
> > 
> > As a workaround I could generate the "all" groups when I create the
> > stem
> > hierarchy. Then I could just select the "all" groups of the child nodes
> > as members of the "all" group in the current node. This would be
> > analogous to the first scenario I described.
> > 
> > Cheers
> > 
> > Loris
> > 
> > --
> > Dr. Loris Bennett
> > Computer Centre
> > Freie Universität Berlin
> > Berlin, Germany
> 
-- 
Dr. Loris Bennett (Mr.)
Freie Universität Berlin
ZEDAT - Zentraleinrichtung für Datenverarbeitung / Computer Center
Compute & Media Service
Fabeckstr. 32, Room 221
D-14195 Berlin
Tel ++49 30 838 51024
Fax ++49 30 838 56721
Email 

Web www.zedat.fu-berlin.de