[Rt-devel] Re: SetOwner speed

[Alexey G Misyurenko]

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

== query id: 1
time: 1150323765.6947
duration: 0.000481843948364258
sql is (SELECT  * FROM Principals WHERE lower(PrincipalType) = ? AND 
ObjectId = ?)

== 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) )

== query id: 5
time: 1150323815.55046
duration: 0.000711202621459961
sql is (SELECT main.* FROM ACL main   WHERE ((main.DelegatedBy = '10')) )

-- 
WBR,   Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP            http://www.caravan.ru
Phone: +7 095 3632252         Cell:  +7 095 5082794