AW: Question about performance on KeywordSelects

Hello,

I’ve done some research on my performance problem (version 2.0.15):
When calling Search/Listing.html,
2 SQL Queries on the Queues table,
1 SQL Queries on the Tickets table,
1 SQL Queries on the KeywordSelects table,
1 SQL Queries on the Users table,
39 SQL Queries on the Watchers table,
1900 SQL Queries on the Keywords table are evaluated.
I admit, that I have quite a lot of keywords defined - but nonetheless: there are more queries than keywords.

So: I’ve still one question and a solution to speed this up by 25%.

What is this ‘up to xy levels deep’ field in the KeywordSelects table? Should this limit the depth of the keywords-search-tree? I have the impression, that this does not work in 2.0.15.

My first solution is a chaching-algorithm: Add the line
%RT::Keywords::KCache=();
to the end of the <%INIT> section of webrt/Admin/Keywords/index.html to delete the cache when the keywords are modified and exchange the file lib/RT/Keywords.pm by:
(The ‘NEXT’-method is new)
#$Header: /pro/CVS/rt/rt-2-0-15/lib/RT/Keywords.pm,v 1.1.1.1 2002/10/11 13:55:30 root Exp $

=head1 NAME

RT::Keywords - a collection of RT::Keyword objects

=head1 SYNOPSIS

use RT::Keywords;
my $keywords = RT::Keywords->new($user);
$keywords->LimitToParent(0);
while my ($keyword = $keywords->Next()) {
print $keyword->Name ."\n";
}

=head1 DESCRIPTION

=head1 METHODS

=begin testing

ok (require RT::TestHarness);
ok (require RT::Keywords);

=end testing

=cut

package RT::Keywords;

use strict;
use vars qw( @ISA %KCache );
use RT::EasySearch;
use RT::Keyword;

@ISA = qw( RT::EasySearch );
%KCache = ();

{{{ sub _Init

sub _Init {
my $self = shift;
$self->{‘table’} = ‘Keywords’;
$self->{‘primary_key’} = ‘id’;

# By default, order by name
$self->OrderBy( ALIAS => 'main',
	    FIELD => 'Name',
	    ORDER => 'ASC');

----------------------------------------

modified by Andreas Warnke @3soft.de

----------------------------------------

# Cache Parameters:
$self->{'Cached'} = 0;
@{$self->{'List'}} = ();

----------------------------------------

return ($self->SUPER::_Init(@_));

}

}}}

{{{ sub _DoSearch

=head2 _DoSearch

A subclass of DBIx::SearchBuilder::_DoSearch that makes sure that _Disabled rows never get seen unless
we’re explicitly trying to see them.

=cut

sub _DoSearch {
my $self = shift;

#unless we really want to find disabled rows, make sure we\'re only finding enabled ones.
unless($self->{'find_disabled_rows'}) {
$self->LimitToEnabled();
} 
my $returnvalue = $self->SUPER::_DoSearch(@_);
return($returnvalue);

}

}}}

{{{ sub NewItem

sub NewItem {
my $self = shift;
return (RT::Keyword->new($self->CurrentUser));
}

}}}

{{{ sub LimitToParent

=head2 LimitToParent

Takes a parent id and limits the returned keywords to children of that parent.

=cut

sub LimitToParent {
my $self = shift;
my $parent = shift;
$self->Limit( FIELD => ‘Parent’,
VALUE => $parent,
OPERATOR => ‘=’,
ENTRYAGGREGATOR => ‘OR’ );
}

}}}

----------------------------------------

added by Andreas Warnke @3soft.de

----------------------------------------

{{{ sub Next

=head2 Next

This is an analysis of the Next-Calls

=cut

sub Next {
my $self = shift;

# DEBUG INFO
open ( DEBUGFILECACHE, '>>/home/install/debugcache.txt');

# get the result, if found in cache 
if ( $self->{'Cached'} == 1 ) {
    print DEBUGFILECACHE "2nd-level-cached entry served.\n";
    close DEBUGFILECACHE;
    return shift @{$self->{'List'}};
}

# lookup cache, if not yet checked:
if ( ! $self->{'Cached'} ) {
    my %restrs = %{$self->{'restrictions'}};
    my $cond = $restrs{'main.Parent'};
    if ( $KCache{$cond} ) {
        if (! $self->{'find_disabled_rows'}) {
            @{$self->{'List'}} = @{$KCache{$cond}};
            $self->{'Cached'} = 1;
            print DEBUGFILECACHE "looked up 1st-level-cache for '$cond', cached entry served.\n";
            close DEBUGFILECACHE;
            return shift @{$self->{'List'}};
        }
        else {
            print DEBUGFILECACHE "Search for cached disabled entries skipped.\n";
            $self->{'Cached'}=3;
        }
    }
    else {
        print DEBUGFILECACHE "looked up 1st-level-cache for '$cond', undef.\n";
        $self->{'Cached'}=2;
    }
}

# get the value from the database:    
my $returnvalue = $self->SUPER::Next(@_); 

# add the value to this objects list:
if ( $returnvalue ) {
    push @{$self->{'List'}}, $returnvalue;
    print DEBUGFILECACHE "stored entry in 2nd-level-cache, entry served.\n";
    close DEBUGFILECACHE;
    return $returnvalue;
}

# add this objects list to the internal cache:
else {
    if ( $self->{'Cached'}!=3 )
    {
        my %restrs2 = %{$self->{'restrictions'}};
        my $cond2 = $restrs2{'main.Parent'};
        @{$KCache{$cond2}} = @{$self->{'List'}};
        print DEBUGFILECACHE "stored in 1st-level-cache under '$cond2', undef served.\n";
        close DEBUGFILECACHE;
        return undef;
    }
    else {
        print DEBUGFILECACHE "Search for disabled not stored in 1st-level-cache, undef served.\n";
        close DEBUGFILECACHE;
        return undef;
    }
}

}

}}}

----------------------------------------

1;-----Ursprüngliche Nachricht-----
Von: Warnke Andreas
Gesendet: Mittwoch, 8. Januar 2003 17:33
An: rt-devel@lists.fsck.com
Betreff: [rt-devel] Question about performance on KeywordSelects

Hello,

Since I defined about 50 global KeywordSelects for each ticket, the
System appears to be quite slow. (on Version 2.0.15) Is the number of 50
too much - or is it more likely that there is an other reason.

Would it make sense to implement a cacheing algorithm for the SQL
queries on the KeywordSelects and the Keyword table?

Thank you for comments.

Andi

Andreas Warnke
3SOFT GmbH, Frauenweiherst. 14, 91058 Erlangen
Tel.: +49-9131-7701-274 mailto:Andreas.Warnke@3SOFT.de
Fax: +49-9131-7701-333 http://www.3SOFT.de

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel