[Rt-devel] Re: SetOwner speed

[Ruslan Zakirov]

Ok, I think index on CachedGroupMembers(MemberId, GroupId, Disabled)
should help you with query 4, but to help you with other queries I
need to see EXPLAINs...

Please, make explains before you'll add index and after.


On 6/15/06, Alexey G Misyurenko <mag at caravan.ru> wrote:
> Alexey G Misyurenko wrote:
> > Ruslan Zakirov wrote:
> >>
> >>>
> >>> >> > Tracing why take ticked (from nobody) operation take so long time
> >>> >> >
> > [skip]
> >>> >> >
> >>> >> And can this call (... First->Delete() ) be omitted when cureent
> >>> owner
> >>> >> is 'Nobody'
> >>> >> and new owner is not 'Nobody'
> >>> > no, as nobody is also user :)
> >>> >
> >>> Ok.
> >>>
> >>> May be is possible to just UPDATE owner of ticked
> >>> not DELETE and INSTERT?
> >> No. Can you grab list of queries that RT executes while this operation?
> >>
> > Sorry, have a bit problems with grabbing SQL request
> > but I trace next time costly call
> >
> > in file lib/RT/GroupMember_Overlay.pm
> >
> > $err = $self->MemberObj->_CleanupInvalidDelegations(InsideTransaction
> > => 1)
> >
> >
> >
> I modify GroupMember_Overlay.pm  to
>
>     $RT::Handle->LogSQLStatements(1);
>     $RT::Handle->ClearSQLStatementLog;
>
>     $err =
> $self->MemberObj->_CleanupInvalidDelegations(InsideTransaction => 1);
>
>     my @log = $RT::Handle->SQLStatementLog;
>     $RT::Handle->ClearSQLStatementLog;
>     $RT::Handle->LogSQLStatements(0);
>
> And  sniff next  SQLs
>
[snip]
>
> == query id: 2
> time: 1150323797.4394
> duration: 31.7433941364288
>
> sql is (SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
> SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
> (ACL.RightName = 'SuperUser' OR  ACL.RightName = 'DelegateRights') AND
> Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
> Principals.id = Groups.id AND Principals.PrincipalType = 'Group' AND
> Principals.id = CachedGroupMembers.GroupId AND
> CachedGroupMembers.MemberId = 10 AND ((ACL.ObjectType = 'RT::System' AND
> ACL.ObjectId = 1) OR (ACL.ObjectType = 'RT::System')) AND
> ACL.PrincipalId = Principals.id AND ACL.PrincipalType = 'Group'  )
> limitquery WHERE rownum <= 1 ) WHERE limitrownum >= 1)
>
> == query id: 3
> time: 1150323808.1467
> duration: 10.7063798904419
> sql is (SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
> SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
> (ACL.RightName = 'SuperUser' OR  ACL.RightName = 'DelegateRights') AND
> Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
> Principals.id = Groups.id AND Principals.PrincipalType = 'Group' AND
> Principals.id = CachedGroupMembers.GroupId AND
> CachedGroupMembers.MemberId = 10 AND ((ACL.ObjectType = 'RT::System' AND
> ACL.ObjectId = 1) OR (ACL.ObjectType = 'RT::System')) AND
> ACL.PrincipalType = Groups.Type AND ((Groups.Domain = 'RT::System-Role'
> AND Groups.Instance = '1') OR (Groups.Domain = 'RT::System-Role'))  )
> limitquery WHERE rownum <= 1 ) WHERE limitrownum >= 1)
>
> == query id: 4
> time: 1150323815.5475
> duration: 7.39867401123047
> sql is (SELECT main.* FROM ( SELECT DISTINCT main.id FROM ACL main ,
> CachedGroupMembers CachedGroupMembers_1  WHERE
> ((CachedGroupMembers_1.MemberId = '10')) AND ((main.PrincipalId =
> CachedGroupMembers_1.GroupId)) AND ((main.RightName =
> 'DelegateRights'))  ) distinctquery, ACL main WHERE (main.id =
> distinctquery.id) )
>
[snip]
-- 
Best regards, Ruslan.