Skip to Content.
Sympa Menu

grouper-dev - RE: [grouper-dev] v2.3 "slow query"

Subject: Grouper Developers Forum

List archive

RE: [grouper-dev] v2.3 "slow query"


Chronological Thread 
  • From: "Black, Carey M." <>
  • To: "Gettes, Michael" <>
  • Cc: "" <>
  • Subject: RE: [grouper-dev] v2.3 "slow query"
  • Date: Wed, 17 Apr 2019 10:39:06 +0000

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
>




Archive powered by MHonArc 2.6.19.

Top of Page