Grouper Developers Forum

["Gettes, Michael" <>]

Only the grouper devs can fix this for now… not suggesting that you do.  I do 
suggest you file a Jira with your info and possibly add into my observations 
and, somehow, link it to GRP-2080 and hopefully the priority g*ds will make 
our issues priorities.

/mrg

> On Apr 17, 2019, at 6:39 AM, Black, Carey M. <> wrote:
> 
> 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 
>> 
>