Grouper Developers Forum

["Black, Carey M." <>]

Michael,

Yes. Composites are my friend at this stage of grouper use. ( Partially 
because I have not yet really crossed the line into good institutional 
reference groups yet. Partially because I want a system that "always gives 
the right answer as of right now". )

My guess is that the query comes from something trying to resolve the 
memberships for a specific composite group.  ( without the nested group 
objects[ AKA: "subject_source <> ‘g:gsa’"] )

While I "speak SQL" and have used several RDBM systems I am far from the 
skill level of a DBA in any RDBMS. Nor am I well versed in hibernate. So, I 
don't know how tweakable some of these queries are going to be. ( Well, maybe 
if a view was created in the DB, then the SQL could be untimely controlled by 
using a class wrapper around the view. I guess that might work out. It just 
punts the RDBMS dependency to creating the view right for that environment. ( 
But what do I know? 😊 ))

-- 
Carey Matthew 

-----Original Message-----
From: Gettes, Michael <> 
Sent: Tuesday, April 16, 2019 7:38 PM
To: Black, Carey M. <>
Cc: 
Subject: Re: [grouper-dev] v2.3 "slow query"

Carey, do you have a large number of composite groups? 
Grouper_composites.type isn’t indexed.  You could try and index it and see if 
that speeds things up.

Additionally, the query has a problem which I have already identified in 
GRP-2080 where “exists” in MySQL servers can be a wee bit slow.  Rewritten 
(in this case) as “and not (1 in (select 1…” would yield much faster response 
and works equally well on Oracle.  Seems like we need to have an evaluation 
of all uses of exists in queries.  Also, the "subject_source <> ‘g:gsa’" 
should be rewritten as “not subject_source = ‘g:gsa’”.

Exactly where in grouper this query is being done - I am not sure but this is 
what I recommend as a fix for us MySQL users.

/mrg

> On Apr 16, 2019, at 10:43 AM, Black, Carey M. <> wrote:
> 
> Using: 
> Grouper: v2.3 (latest patches)
> RDBMS: Mysql 10.2.16 ( MariaDB)
> 
> My DBA is telling me that this query is showing up in the slow log ( this 
> one is taking about 75 seconds, and happening "often" )
> 
> Example search:
> 
> select distinct composite2_.owner as col_0_0_, composite2_.id as col_1_0_, 
> member1_.id as col_2_0_ 
> from grouper_memberships_all_v membership0_ 
>       cross join grouper_members member1_ 
>       cross join grouper_composites composite2_ 
> where composite2_.type='union' 
>       and (composite2_.left_factor=membership0_.owner_group_id or 
> composite2_.right_factor=membership0_.owner_group_id) 
>       and membership0_.field_id='6142a7efff744cfd8a9dae4058c5d64b' 
>       and membership0_.immediate_mship_enabled='T' 
>       and membership0_.member_id=member1_.id 
>       and member1_.subject_source<>'g:gsa' 
>       and  not (exists (
>               select 1 
>               from grouper_memberships immediatem3_ 
>               where immediatem3_.owner_group_id=composite2_.owner 
>                       and immediatem3_.member_id=member1_.id 
>                       and 
> immediatem3_.field_id='6142a7efff744cfd8a9dae4058c5d64b' 
>                       and immediatem3_.mship_type='composite' 
>                       and immediatem3_.enabled='T'
>                       ));
> 
> Can you identify what in grouper is making that query?
> Is there anything that could be done to improve the performance of that 
> query? ( a new index maybe? )
> 
> -- 
> Carey Matthew 
>