[Rt-devel] Re: SetOwner speed

[Ruslan Zakirov]

I spotted "AND (   (acl.objecttype = 'RT::System' AND acl.objectid =
1) OR (acl.objecttype = 'RT::System') )" which is really wierd and
should be fixed in RT code. This should be just "AND (acl.objecttype =
'RT::System' AND acl.objectid = 1)", without OR ... part, but I don't
think this change would give much speed improvement.

Also, I didn't know you're using Oracle. I can't read its explains,
sorry. From oracle's docs I see that it has output formats of explains
which have numbers of rows oracle scans. Such explain could be more
informative.

On 6/17/06, Alexey G Misyurenko <mag at caravan.ru> wrote:
> 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
>
>
> _______________________________________________
> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
>
> Best Practical is hiring! Come hack Perl for us: http://bestpractical.com/about/jobs.html
>


-- 
Best regards, Ruslan.