[Rt-devel] Query Slowness (was: 3.4.0rc1 slow)

[Jesse Vincent]

On Sat, Feb 19, 2005 at 12:54:04PM -0800, dustin at cnw.com wrote:
> David's Thread :
> 
> http://lists.bestpractical.com/pipermail/rt-devel/2005-January/006663.html
> 
> I took a look at the patch you posted in this post :
> 
> http://lists.bestpractical.com/pipermail/rt-devel/2005-January/006709.html
> 
> But it looks like the performance problem he had was when there was no 
> object passed to the SelectOwner component.

This is an issue that someone reported. I proposed a fix. Everyone who's
tried the fix (which is more extensive than your first glance, I
believe) has told me that it's resulted in a massive performance
improvement. Give it a shot and tell me if I'm wrong? Regardless, we're
unlikely to do performance work on 3.0, except for customers who can't
upgrade.    

	Jesse

> 
> Dustin
> 
> On Sat, 19 Feb 2005, Jesse Vincent wrote:
> 
> > Can you send a pointer to David's message? I can't find it in the
> > archive?
> > 
> > 
> > On Sat, Feb 19, 2005 at 03:03:42AM -0800, dustin at cnw.com wrote:
> > > On Fri, 18 Feb 2005, Jesse Vincent wrote:
> > > 
> > > > 
> > > > 
> > > > 
> > > > On Fri, Feb 18, 2005 at 05:18:09PM -0800, dustin at cnw.com wrote:
> > > > > Hello,
> > > > > 
> > > > > Our instance of RT is suffering from the exact same issue. It happens 
> > > > > anytime an owner dropdown box is rendered. RT 3.0.11, MySQL 4.0.16, 
> > > > > SearchBuilder p5-DBIx-SearchBuilder-0.99
> > > > 
> > > > So. We did a lot to fix this in 3.4.0 and newer searchbuilders.
> > > > Upgrading might be worth a shot.
> > > > 
> > > 
> > > The thing that scares me is that David and his tests with 3.4.0 show the 
> > > same issue with the same query using the new RT and the new version of 
> > > SearchBuilder.
> > > 
> > > > > 
> > > > > David Kerry wrote :
> > > > > 
> > > > > > LOG:  duration: 1983.993 ms  statement:                                                                                         
> > > > > > SELECT DISTINCT main.* FROM (Groups main LEFT JOIN 
> > > > > > Principals Principals_2 ON ( main.id = Principals_2.id)),
> > > > > > ACL ACL_1 WHERE ((ACL_1.RightName = 'OwnTicket')) AND 
> > > > > > ((Principals_2.Disabled = '0')) AND ( ( ACL_1.PrincipalId = main.id AND
> > > > > > ACL_1.PrincipalType = 'Group' AND ( main.Domain = 'SystemInternal' OR 
> > > > > > main.Domain = 'UserDefined' OR main.Domain =
> > > > > > 'ACLEquivalence')) OR ( ( (main.Domain = 'RT::Queue-Role' AND 
> > > > > > main.Instance = 102) ) AND main.Type = ACL_1.PrincipalType) ) AND
> > > > > > (ACL_1.ObjectType = 'RT::System' OR (ACL_1.ObjectType = 'RT::Queue' AND 
> > > > > > ACL_1.ObjectId = 102) ) ORDER BY main.Name ASC
> > > > > 
> > > > > Here's ours :
> > > > > 
> > > > > # Time: 050218 16:12:22
> > > > > # User at Host: rt_user[rt_user] @ localhost []
> > > > > # Query_time: 5  Lock_time: 0  Rows_sent: 17  Rows_examined: 72
> > > > > SELECT DISTINCT main.* FROM ( Groups main LEFT JOIN Principals as 
> > > > > Principals_2  ON  ( main.id = Principals_2.id)), ACL ACL_1  WHERE 
> > > > > ((ACL_1.RightName = 'OwnTicket')OR(ACL_1.RightName = 'SuperUser')) AND 
> > > > > ((Principals_2.Disabled = '0')) AND
> > > > >           ( (    ACL_1.PrincipalId = main.id
> > > > >              AND ACL_1.PrincipalType = 'Group'
> > > > >              AND (   main.Domain = 'SystemInternal'
> > > > >                   OR main.Domain = 'UserDefined'
> > > > >                   OR main.Domain = 'ACLEquivalence'))
> > > > >             OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 3)  
> > > > > OR ( main.Domain = 'RT::Ticket-Role' AND main.Instance = 410374)  )  AND 
> > > > > main.Type = ACL_1.PrincipalType) )
> > > > >          AND (ACL_1.ObjectType = 'RT::System' OR  (ACL_1.ObjectType = 
> > > > > 'RT::Queue' AND ACL_1.ObjectId = 3) ) ORDER BY main.Name ASC;
> > > > > 
> > > > > Output of a DESCRIBE on that query :
> > > > > 
> > > > > table, type, possible_keys, key, key_len, ref, rows, Extra
> > > > > ---------------------------------------------------------------
> > > > > ACL_1, range, ACL1, ACL1, 54, NULL, 19, Using where; Using index; Using temporary; Using filesort
> > > > > main, ALL, PRIMARY, Groups1, Groups2, NULL, NULL, NULL ,576818, Using where
> > > > > Principals_2, eq_ref, PRIMARY,PRIMARY, 4, main.id, 1, Using where; Distinct                                     
> > > > > 
> > > > > Yikes!
> > > > > _______________________________________________
> > > > > Rt-devel mailing list
> > > > > Rt-devel at lists.bestpractical.com
> > > > > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
> > > > > 
> > > > 
> > > > 
> > > 
> > 
> > 
> 

--