[Rt-devel] Re: SetOwner speed

[Alexey G Misyurenko]

Alexey G Misyurenko wrote:
> Ruslan Zakirov wrote:
>> On 6/15/06, Alexey G Misyurenko <mag at caravan.ru> wrote:
>>> Ruslan Zakirov wrote:
>>> > 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...
>>> >
> Sorry for the pause with answer - second oracle installation take a 
> time...
>
>>> > Please, make explains before you'll add index and after.
>>> >
>
>
>  WITH INDEX
>  ----------
>  SQL Statement from editor:
>  
>  
SQL Tunning toolkit for oracle just finish analise what can be done with 
original
SQL request generated by RT .

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

which is run 9,353s, and propose next SQL edition


SELECT /*+ PARALLEL(, 2) */
       *
  FROM (SELECT /*+ PARALLEL(LIMITQUERY, 2) */
               limitquery.*, ROWNUM limitrownum
          FROM (SELECT /*+ PARALLEL(CACHEDGROUPMEMBERS, 2) */
                       acl.ID
                  FROM acl, GROUPS, principals, cachedgroupmembers
                 WHERE 0 = principals.disabled
                   AND 0 = cachedgroupmembers.disabled
                   AND GROUPS.ID = principals.ID
                   AND 'Group' = principals.principaltype
                   AND cachedgroupmembers.groupid = principals.ID
                   AND 10 = cachedgroupmembers.memberid
                   AND principals.ID = acl.principalid
                   AND 'Group' = acl.principaltype
                   AND principals.disabled = cachedgroupmembers.disabled
                   AND cachedgroupmembers.groupid = GROUPS.ID
                   AND acl.principalid = GROUPS.ID
                   AND principals.principaltype = acl.principaltype
                   AND acl.principalid = cachedgroupmembers.groupid
                   AND (   'RT::System' = acl.objecttype AND 1 = 
acl.objectid
                        OR 'RT::System' = acl.objecttype
                       )
                   AND (   'SuperUser' = acl.rightname
                        OR 'DelegateRights' = acl.rightname
                       )) limitquery
         WHERE 1 >= ROWNUM)
 WHERE 1 <= limitrownum

which take 5.391s to run.

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