> On Tue, Sep 16, 2008 at 2:31 AM, Mauri Sahlberg
> <Mauri.Sahlberg@claymountain.com> wrote:
> > Hi,
> > We just upgraded Best Practical's RT from 3.6 to 3.81 and gave the
> > database completely own machine. And the users still complain that it is
> > dog slow.
> Moved up from below:
> > Version : 8.1.11 Vendor: CentOS
> So, you built it its own machine, but you didn't upgrade to at least 8.2?
> Last place I worked we ran rt 3.6.1 and got a noticeable performance
> boost from switching to 8.2 but the only thing that was ever really
> slow was viewing the rather large approval queue.
> > :-( I installed pg_top and it seems that at the beginning of
> > the ticket display RT-issues a query that eats everything the database
> > has. Query is as follows:
> > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_2 JOIN
> > Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
> > CachedGroupMembers CachedGroupMembers_3 ON
> > ( CachedGroupMembers_3.MemberId = Principals_1.id ) WHERE
> > (Principals_1.Disabled = '0') AND (ACL_2.PrincipalId =
> > CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND
> > (ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType =
> > 'User') AND (ACL_2.RightName = 'OwnTicket') AND ((ACL_2.ObjectType =
> > 'RT::Queue' AND ACL_2.ObjectId = 18) OR (ACL_2.ObjectType =
> > 'RT::System')) ORDER BY main.Name ASC
What is your version of DBIx-SearchBuilder? In particular, the
DISTINCT definition was changed from:
$$statementref = "SELECT DISTINCT main.* FROM $$statementref";
You can change the line in your version of SearchBuilder and restart
RT with a cache flush to get approximately the same benefit.