[Rt-devel] slow response opening QueryBuilder in RT 3.6.0

[Mittag, Christian]

On Sun, 6 Aug 2006 12:02:18 +0200 christian.mittag at siemens.com wrote:

> Hi,
>
> We are just upgrading from RT 3.0.6 with 60k tickets to RT 3.6.0rc2.
> We are using mysql 5.0.22.
>
> If one opens the "Query Builder" for the first time, it takes about
> 20 sec to generate the list of "owner" (the result: 100 owner from 
> 2000000 rows).
>
> Is there a way to get that query done with
> a straight_join and a given table order or
> is there some other fix or workaround to get better
> performance generating the list of "owner"?
>

As a workaround we are using now a special select-query for
just generating the list of owners for the QueryBuilder.
The tables are arranged in an special order just for these queries.   
Now mysql 5.0.22 is about 20 times faster.

The wokaround looks like that:

There is a new
lib/RT/Users_special.pm:
------------------------

package RT::Users_special;
use base qw/RT::Users/;

my %joins = ( 'Principals Principals_1 CachedGroupMembers
CachedGroupMembers_2 Groups Groups_3 ACL ACL_4', => ' Groups G
roups_3, CachedGroupMembers CachedGroupMembers_2, Principals
Principals_1,  ACL ACL_4, Users main ',
              'Principals Principals_1 ACL ACL_2 CachedGroupMembers
CachedGroupMembers_3' => ' Principals Principals_1,
ACL ACL_2, CachedGroupMembers CachedGroupMembers_3, Users main ',
            );

sub _BuildJoins{
    my $self = shift;
    my $st= join ' ',@{ $self->{'aliases'}};

    return $joins{$st} if defined $joins{$st};
    $self->SUPER::_BuildJoins();
}

1;

========================================================================
===========================
This new RT::Users_special is just used in
share/html/Elements/SelectOwner:
--------------------------------

--- SelectOwner.orig  
+++ SelectOwner 
@@ -58,6 +58,7 @@

 <%INIT>
+use RT::Users_special;
 my @objects;

@@ -80,7 +81,7 @@

 foreach my $object (@objects) {
-       my $Users = RT::Users->new($session{CurrentUser});
+       my $Users = RT::Users_special->new($session{CurrentUser});
         $Users->WhoHaveRight(Right => 'OwnTicket', Object => $object,
IncludeSystemRights => 1, IncludeSuperusers => 0);
         while (my $User = $Users->Next()) {

========================================================================
===========================


Best regards
Christian Mittag