AW: [rt-devel] Question about performance on KeywordSelects

[Warnke Andreas]

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 at 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 at 3SOFT.de
Fax: +49-9131-7701-333 http://www.3SOFT.de
 
_______________________________________________
rt-devel mailing list
rt-devel at lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel