Help solving ObjectCustomFieldValue performance problem (maybe impact RT 4.4 upgrade)

Loos_Christian · January 27, 2016, 1:32pm

Hi,

we have a external CF (type Select) where we don’t want that users can
add the same value to different tickets.

I implemented this by building a list of the used values:

my %used_values;
my $ocfvs = RT::ObjectCustomFieldValues->new(RT->SystemUser);
$ocfvs->LimitToCustomField(1);
while (my $ocfv = $ocfvs->Next) {
$used_values{ $ocfv->Content } = 1;
}

After this I noticed much slower page loadtimes for the ticket create
and modify pages.
Investigating this with the, by the way wonderful and very helpful, “SQL
Queries” tool, it shows that for every create/modify page load RT runs
900 queries against the Tickets table.
The 900 comes from 900 tickets which have a CF.1 value (select
count(distinct ObjectId) from ObjectCustomFieldValues where CustomField
= 1 and Disabled = 0;)
Investigating further shows that Content [1] calls CustomFieldObj [2]
which SetContextObject based on Object [3] which loads, in my case, the
ticket.

I was really surprised to see queries against the Tickets table because
I used the RT::ObjectCustomFieldValues to fetch the values directly,
without going the other way around starting from tickets.

Actually removing SetContextObject from the CustomFieldObj method avoids
the queries against the Tickets table. This was introduced by [4], but
the commit message says nothing about the reason for this. I assume this
was necessary for the right check in Content [5] to also account for
ticket or queue role level rights.

If you look at the Content method [1], there are also multiple calls for
“$self->CustomFieldObj->Type” to fetch the CF type. This also involves
loading the object, which isn’t necessary, as CF can’t have a different
type depending on the object they are applied to.

I hope to get some feedback from the RT developers regarding this.

I think this could also have an impact on the RT 4.4 upgrade, because
the 4.3.6 upgrade step [6] calls RT::ObjectCustomFieldValues->Content
which maybe slow down the upgrade if you have many CF values with Category.

Chris

[1]

github.com

bestpractical/rt/blob/stable/lib/RT/ObjectCustomFieldValue.pm#L221


      
              );
          }
          
          
=head2 CustomFieldObj
          
          
Returns the CustomField Object which has the id returned by CustomField
          
          
=cut
          
          
sub CustomFieldObj {
              my $self = shift;
              my $CustomField = RT::CustomField->new( $self->CurrentUser );
              $CustomField->SetContextObject( $self->Object );
              $CustomField->Load( $self->__Value('CustomField') );
              return $CustomField;
          }
          
          

          
=head2 Content
          
          
Return this custom field's content. If there's no "regular"

[2]

github.com

bestpractical/rt/blob/stable/lib/RT/ObjectCustomFieldValue.pm#L202


      
          }
          
          
sub LoadByObjectContentAndCustomField {
              my $self = shift;
              my %args = (
                  Object => undef,
                  CustomField => undef,
                  Content => undef,
                  @_
              );
          
          
    my $obj = $args{'Object'} or return;
          
          
    return $self->LoadByCols(
                  Content => $args{'Content'},
                  CustomField => $args{'CustomField'},
                  ObjectType => ref($obj),
                  ObjectId => $obj->Id,
                  Disabled => 0
              );
          }

[3]

github.com

bestpractical/rt/blob/stable/lib/RT/ObjectCustomFieldValue.pm#L274


      
                  my $eIP = sprintf "%d.%d.%d.%d", split /\./, $1;
                  if ( $content eq $eIP ) {
                      return $content;
                  }
                  else {
                      return $content . "-" . $eIP;
                  }
              }
              elsif ( $large_content =~ /^\s*($IPv6_re)\s*$/o ) {
                  my $eIP = Net::IP::ip_compress_address($1, 6);
                  if ( $content eq $eIP ) {
                      return $content;
                  }
                  else {
                      return $content . "-" . $eIP;
                  }
              }
              else {
                  return $content;
              }
          }

[4]

[5]

github.com

bestpractical/rt/blob/stable/lib/RT/ObjectCustomFieldValue.pm#L224


      
          =head2 CustomFieldObj
          
          
Returns the CustomField Object which has the id returned by CustomField
          
          
=cut
          
          
sub CustomFieldObj {
              my $self = shift;
              my $CustomField = RT::CustomField->new( $self->CurrentUser );
              $CustomField->SetContextObject( $self->Object );
              $CustomField->Load( $self->__Value('CustomField') );
              return $CustomField;
          }
          
          

          
=head2 Content
          
          
Return this custom field's content. If there's no "regular"
          content, try "LargeContent"
          
          
=cut

[6]

github.com

bestpractical/rt/blob/master/etc/upgrade/4.3.6/content

use strict;
use warnings;

our @Initial = (
    sub {
        use RT::CustomFields;
        my $CFs = RT::CustomFields->new( RT->SystemUser );
        $CFs->UnLimit;
        $CFs->Limit( FIELD => 'Type', VALUE => 'Select' );

        my $seen;
        while (my $cf  = $CFs->Next ) {
            next if $cf->BasedOnObj->Id;
            my @categories;
            my %mapping;
            my $values = $cf->Values;
            while (my $value = $values->Next) {
                next unless defined $value->Category and length $value->Category;
                push @categories, $value->Category unless grep {$_ eq $value->Category} @categories;
                $mapping{$value->Name} = $value->Category;

This file has been truncated. show original