adds / removes dbix prefetches where applicable to improve database performance
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/commit/d1a42eac Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/tree/d1a42eac Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/diff/d1a42eac Branch: refs/heads/master Commit: d1a42eac900bafb8cc0cf8c89e0962ae34103867 Parents: b733479 Author: Jeremy Mitchell <mitchell...@gmail.com> Authored: Wed Feb 8 14:05:31 2017 -0700 Committer: Dewayne Richardson <dewr...@apache.org> Committed: Wed Feb 8 14:31:48 2017 -0700 ---------------------------------------------------------------------- traffic_ops/app/lib/UI/Asn.pm | 22 +------------------- traffic_ops/app/lib/UI/Cachegroup.pm | 2 -- traffic_ops/app/lib/UI/CachegroupParameter.pm | 2 +- traffic_ops/app/lib/UI/ConfigFiles.pm | 12 +++++------ traffic_ops/app/lib/UI/DeliveryServiceServer.pm | 10 ++++----- traffic_ops/app/lib/UI/Federation.pm | 4 ++-- traffic_ops/app/lib/UI/HwInfo.pm | 2 +- traffic_ops/app/lib/UI/Job.pm | 6 +++--- traffic_ops/app/lib/UI/PhysLocation.pm | 4 ++-- traffic_ops/app/lib/UI/ProfileParameter.pm | 2 +- traffic_ops/app/lib/UI/Server.pm | 6 +++--- traffic_ops/app/lib/UI/SslKeys.pm | 2 +- traffic_ops/app/lib/UI/StaticDnsEntry.pm | 2 +- traffic_ops/app/lib/UI/Steering.pm | 2 +- 14 files changed, 28 insertions(+), 50 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/Asn.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/Asn.pm b/traffic_ops/app/lib/UI/Asn.pm index 2b03c9b..c4a7738 100644 --- a/traffic_ops/app/lib/UI/Asn.pm +++ b/traffic_ops/app/lib/UI/Asn.pm @@ -162,7 +162,7 @@ sub view { &stash_role($self); $self->stash( asn_data => {}, cg_data => {} ); - my $rs = $self->db->resultset('Asn')->search( { id => $id } ); + my $rs = $self->db->resultset('Asn')->search( { id => $id }, { prefetch => [ 'cachegroup' ] } ); my $data = $rs->single; my $cache_group_name = $data->cachegroup->name; @@ -197,24 +197,4 @@ sub get_asns { return \%data; } -# Read - get through API -# sub index { -# my $self = shift; -# my @data; -# my $orderby = "asn"; -# $orderby = $self->param('orderby') if ( defined $self->param('orderby') ); -# my $rs_data = $self->db->resultset("Asn")->search( undef, { prefetch => [ { 'cachegroup' => undef } ], order_by => $orderby } ); -# while ( my $row = $rs_data->next ) { -# push( -# @data, { -# "id" => $row->id, -# "asn" => $row->asn, -# "cachegroup" => $row->cachegroup->name, -# "last_updated" => $row->last_updated, -# } -# ); -# } -# $self->render( json => \@data ); -# } - 1; http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/Cachegroup.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/Cachegroup.pm b/traffic_ops/app/lib/UI/Cachegroup.pm index a3d0974..7662212 100644 --- a/traffic_ops/app/lib/UI/Cachegroup.pm +++ b/traffic_ops/app/lib/UI/Cachegroup.pm @@ -98,8 +98,6 @@ sub view { secondary_parent_name => $secondary_parent_name, ); - # $self->stash( cg_data => $data ); - &stash_role($self); $self->stash( fbox_layout => 1, cg_data => $data, selected_type => $selected_type ); http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/CachegroupParameter.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/CachegroupParameter.pm b/traffic_ops/app/lib/UI/CachegroupParameter.pm index b85b418..8aec8eb 100644 --- a/traffic_ops/app/lib/UI/CachegroupParameter.pm +++ b/traffic_ops/app/lib/UI/CachegroupParameter.pm @@ -24,7 +24,7 @@ sub index { my $self = shift; my @data; my $orderby = $self->param('orderby') || "cachegroup"; - my $rs_data = $self->db->resultset("CachegroupParameter")->search( undef, { order_by => $orderby } ); + my $rs_data = $self->db->resultset("CachegroupParameter")->search( undef, { prefetch => [ 'cachegroup', 'parameter' ], order_by => $orderby } ); while ( my $row = $rs_data->next ) { push( @data, { http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/ConfigFiles.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/ConfigFiles.pm b/traffic_ops/app/lib/UI/ConfigFiles.pm index 69498f8..efe9850 100644 --- a/traffic_ops/app/lib/UI/ConfigFiles.pm +++ b/traffic_ops/app/lib/UI/ConfigFiles.pm @@ -151,10 +151,10 @@ sub server_data { my $server; if ( $id =~ /^\d+$/ ) { - $server = $self->db->resultset('Server')->search( { id => $id } )->single; + $server = $self->db->resultset('Server')->search( { id => $id }, { prefetch => [ 'profile', 'type', 'cachegroup', 'cdn' ] } )->single; } else { - $server = $self->db->resultset('Server')->search( { host_name => $id } )->single; + $server = $self->db->resultset('Server')->search( { host_name => $id }, { prefetch => [ 'profile', 'type', 'cachegroup', 'cdn' ] } )->single; } return $server; @@ -1411,13 +1411,13 @@ sub header_rewrite_dot_config { my $ds_xml_id = undef; if ( $file =~ /^hdr_rw_mid_(.*)\.config$/ ) { $ds_xml_id = $1; - my $ds = $self->db->resultset('Deliveryservice')->search( { xml_id => $ds_xml_id }, { prefetch => [ 'type', 'profile' ] } )->single(); + my $ds = $self->db->resultset('Deliveryservice')->search( { xml_id => $ds_xml_id } )->single(); my $actions = $ds->mid_header_rewrite; $text .= $actions . "\n"; } elsif ( $file =~ /^hdr_rw_(.*)\.config$/ ) { $ds_xml_id = $1; - my $ds = $self->db->resultset('Deliveryservice')->search( { xml_id => $ds_xml_id }, { prefetch => [ 'type', 'profile' ] } )->single(); + my $ds = $self->db->resultset('Deliveryservice')->search( { xml_id => $ds_xml_id } )->single(); my $actions = $ds->edge_header_rewrite; $text .= $actions . "\n"; } @@ -1438,7 +1438,7 @@ sub regex_remap_dot_config { if ( $file =~ /^regex_remap_(.*)\.config$/ ) { my $ds_xml_id = $1; - my $ds = $self->db->resultset('Deliveryservice')->search( { xml_id => $ds_xml_id }, { prefetch => [ 'type', 'profile' ] } )->single(); + my $ds = $self->db->resultset('Deliveryservice')->search( { xml_id => $ds_xml_id } )->single(); $text .= $ds->regex_remap . "\n"; } @@ -1509,7 +1509,7 @@ sub ssl_multicert_dot_config { foreach my $ds (@ds_list) { my $ds_id = $ds->id; my $xml_id = $ds->xml_id; - my $rs_ds = $self->db->resultset('Deliveryservice')->search( { 'me.id' => $ds_id } ); + my $rs_ds = $self->db->resultset('Deliveryservice')->search( { 'me.id' => $ds_id }, { prefetch => ['type'] } ); my $data = $rs_ds->single; my $domain_name = UI::DeliveryService::get_cdn_domain( $self, $ds_id ); my $ds_regexes = UI::DeliveryService::get_regexp_set( $self, $ds_id ); http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/DeliveryServiceServer.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/DeliveryServiceServer.pm b/traffic_ops/app/lib/UI/DeliveryServiceServer.pm index fefa1f9..b9f85bb 100644 --- a/traffic_ops/app/lib/UI/DeliveryServiceServer.pm +++ b/traffic_ops/app/lib/UI/DeliveryServiceServer.pm @@ -28,7 +28,7 @@ sub cpdss_iframe { my $srvr_id = $self->param('id'); if ( $mode eq "view" ) { - my $server = $self->db->resultset('Server')->search( { 'me.id' => $srvr_id } )->single(); + my $server = $self->db->resultset('Server')->search( { 'me.id' => $srvr_id }, { prefetch => [ 'cachegroup' ] } )->single(); my @etypeids = &type_ids( $self, 'EDGE%', 'server' ); my $rs = $self->db->resultset('Server')->search( { type => { -in => \@etypeids }, cdn_id => $server->cdn_id }, { prefetch => 'profile', order_by => 'host_name' } ); @@ -64,12 +64,12 @@ sub edit { # Get list of server ids associated with ds my $assigned_servers; - my $rsas = $self->db->resultset('DeliveryserviceServer')->search( { deliveryservice => $id } ); + my $rsas = $self->db->resultset('DeliveryserviceServer')->search( { deliveryservice => $id }, { prefetch => [ 'server' ] } ); while ( my $row = $rsas->next ) { $assigned_servers->{ $row->server->id } = 1; } - my $ds = $self->db->resultset('Deliveryservice')->search( { 'me.id' => $id }, { prefetch => 'cdn' } )->single(); + my $ds = $self->db->resultset('Deliveryservice')->search( { 'me.id' => $id } )->single(); my $valid_profiles; my $psas = $self->db->resultset('Server')->search( { cdn_id => $ds->cdn_id }, @@ -82,7 +82,7 @@ sub edit { $valid_profiles->{$row} = 1; } - $ds = $self->db->resultset('Deliveryservice')->search( { id => $id } )->single(); + $ds = $self->db->resultset('Deliveryservice')->search( { id => $id }, { prefetch => [ 'cdn' ] } )->single(); my @types; push(@types, &type_ids( $self, 'EDGE%', 'server' ) ); @@ -133,7 +133,7 @@ sub read { my $limit = 10; $orderby = $self->param('orderby') if ( defined $self->param('orderby') ); $limit = $self->param('limit') if ( defined $self->param('limit') ); - my $rs_data = $self->db->resultset("DeliveryserviceServer")->search( undef, { order_by => $orderby, rows => $limit } ); + my $rs_data = $self->db->resultset("DeliveryserviceServer")->search( undef, { prefetch => [ 'deliveryservice', 'server' ], order_by => $orderby, rows => $limit } ); while ( my $row = $rs_data->next ) { push( @data, { http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/Federation.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/Federation.pm b/traffic_ops/app/lib/UI/Federation.pm index bb925e3..3485095 100644 --- a/traffic_ops/app/lib/UI/Federation.pm +++ b/traffic_ops/app/lib/UI/Federation.pm @@ -77,7 +77,7 @@ sub edit { my $role_name; my $user_id; - my $ftusers = $self->db->resultset('FederationTmuser')->search( { federation => $fed_id }, { prefetch => [ 'federation', 'tm_user' ] } ); + my $ftusers = $self->db->resultset('FederationTmuser')->search( { federation => $fed_id }, { prefetch => [ 'federation', 'tm_user', 'role' ] } ); while ( my $ft = $ftusers->next ) { $user_id = $ft->tm_user->id; $role_name = $ft->role->name; @@ -158,7 +158,7 @@ sub group_resolvers { my $data; my $fed_fed_resolvers = - $self->db->resultset('FederationFederationResolver')->search( { federation => $fed_id }, { prefetch => ['federation_resolver'] } ); + $self->db->resultset('FederationFederationResolver')->search( { federation => $fed_id }, { prefetch => [ { 'federation_resolver' => 'type' } ] } ); my $resolvers; while ( my $row = $fed_fed_resolvers->next ) { my $fed_resolver = $row->federation_resolver; http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/HwInfo.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/HwInfo.pm b/traffic_ops/app/lib/UI/HwInfo.pm index 754d24d..9bb7c7b 100644 --- a/traffic_ops/app/lib/UI/HwInfo.pm +++ b/traffic_ops/app/lib/UI/HwInfo.pm @@ -26,7 +26,7 @@ sub readhwinfo { my $limit = $self->param('limit') || 1000; # get list of servers in one query - my $rs_data = $self->db->resultset("Hwinfo")->search( undef, { rows => $limit } ); + my $rs_data = $self->db->resultset("Hwinfo")->search( undef, { prefetch => [ 'serverid' ], rows => $limit } ); while ( my $row = $rs_data->next ) { my $id = $row->id; http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/Job.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/Job.pm b/traffic_ops/app/lib/UI/Job.pm index 757cfcd..063d3ce 100644 --- a/traffic_ops/app/lib/UI/Job.pm +++ b/traffic_ops/app/lib/UI/Job.pm @@ -356,7 +356,7 @@ sub readresult { my $id = $self->param('id'); my @data; - my $rs_data = $self->db->resultset('JobResult')->search( { job => $self->param('id') } ); + my $rs_data = $self->db->resultset('JobResult')->search( { job => $self->param('id') }, { prefetch => [ 'job', 'agent' ] } ); while ( my $row = $rs_data->next ) { my %hash = ( id => $row->id, @@ -432,10 +432,10 @@ sub viewagentjob { my $rs_data; if ( $id eq "all" ) { - $rs_data = $self->db->resultset('Job')->search( { status => 1 }, { order_by => 'me.' . $orderby, } ); + $rs_data = $self->db->resultset('Job')->search( { status => 1 }, { prefetch => [ 'agent', 'status', 'job_user' ], order_by => 'me.' . $orderby, } ); } else { - $rs_data = $self->db->resultset('Job')->search( { agent => $id, status => 1 }, { order_by => 'me.' . $orderby, } ); + $rs_data = $self->db->resultset('Job')->search( { agent => $id, status => 1 }, { prefetch => [ 'agent', 'status', 'job_user' ], order_by => 'me.' . $orderby, } ); } while ( my $row = $rs_data->next ) { http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/PhysLocation.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/PhysLocation.pm b/traffic_ops/app/lib/UI/PhysLocation.pm index 06f220d..54a643d 100644 --- a/traffic_ops/app/lib/UI/PhysLocation.pm +++ b/traffic_ops/app/lib/UI/PhysLocation.pm @@ -62,7 +62,7 @@ sub readphys_location { sub readphys_locationtrimmed { my $self = shift; my @data; - my $rs_data = $self->db->resultset("PhysLocation")->search( undef, { prefetch => ['region'], order_by => 'me.name' } ); + my $rs_data = $self->db->resultset("PhysLocation")->search( undef, { order_by => 'me.name' } ); while ( my $row = $rs_data->next ) { next if $row->short_name eq 'UNDEF'; @@ -79,7 +79,7 @@ sub readphys_locationtrimmed { sub readregion { my $self = shift; my @data; - my $rs_data = $self->db->resultset("Region")->search( undef, { prefetch => ['division'], order_by => 'me.name' } ); + my $rs_data = $self->db->resultset("Region")->search( undef, { order_by => 'me.name' } ); while ( my $row = $rs_data->next ) { push( @data, { http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/ProfileParameter.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/ProfileParameter.pm b/traffic_ops/app/lib/UI/ProfileParameter.pm index 6c35cfe..307fd09 100644 --- a/traffic_ops/app/lib/UI/ProfileParameter.pm +++ b/traffic_ops/app/lib/UI/ProfileParameter.pm @@ -26,7 +26,7 @@ sub read { my $self = shift; my @data; my $orderby = $self->param('orderby') || "profile"; - my $rs_data = $self->db->resultset("ProfileParameter")->search( undef, { order_by => $orderby } ); + my $rs_data = $self->db->resultset("ProfileParameter")->search( undef, { prefetch => [ 'profile', 'parameter' ], order_by => $orderby } ); while ( my $row = $rs_data->next ) { push( @data, { http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/Server.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/Server.pm b/traffic_ops/app/lib/UI/Server.pm index 051ad47..8103903 100644 --- a/traffic_ops/app/lib/UI/Server.pm +++ b/traffic_ops/app/lib/UI/Server.pm @@ -895,17 +895,17 @@ sub readupdate { my $rs_servers; my %parent_pending = (); if ( $host_name =~ m/^all$/ ) { - $rs_servers = $self->db->resultset("Server")->search(undef); + $rs_servers = $self->db->resultset("Server")->search(undef, { prefetch => [ 'type', 'cachegroup' ] } ); } else { $rs_servers = - $self->db->resultset("Server")->search( { host_name => $host_name } ); + $self->db->resultset("Server")->search( { host_name => $host_name }, { prefetch => [ 'type', 'cachegroup' ] } ); my $count = $rs_servers->count(); if ( $count > 0 ) { if ( $rs_servers->single->type->name =~ m/^EDGE/ ) { my $parent_cg = $self->db->resultset('Cachegroup')->search( { id => $rs_servers->single->cachegroup->id } )->get_column('parent_cachegroup_id')->single; - my $rs_parents = $self->db->resultset('Server')->search( { cachegroup => $parent_cg } ); + my $rs_parents = $self->db->resultset('Server')->search( { cachegroup => $parent_cg }, { prefetch => [ 'status'] } ); while ( my $prow = $rs_parents->next ) { if ( $prow->upd_pending == 1 && $prow->status->name ne "OFFLINE" ) http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/SslKeys.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/SslKeys.pm b/traffic_ops/app/lib/UI/SslKeys.pm index cb19de2..d583a38 100644 --- a/traffic_ops/app/lib/UI/SslKeys.pm +++ b/traffic_ops/app/lib/UI/SslKeys.pm @@ -28,7 +28,7 @@ use MIME::Base64; sub add { my $self = shift; my $ds_id = $self->param('id'); - my $rs_ds = $self->db->resultset('Deliveryservice')->search( { 'me.id' => $ds_id } ); + my $rs_ds = $self->db->resultset('Deliveryservice')->search( { 'me.id' => $ds_id }, { prefetch => [ 'cdn', 'type' ] } ); my $data = $rs_ds->single; my $xml_id = $data->xml_id; &stash_role($self); http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/StaticDnsEntry.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/StaticDnsEntry.pm b/traffic_ops/app/lib/UI/StaticDnsEntry.pm index 9a2a339..8d45a90 100644 --- a/traffic_ops/app/lib/UI/StaticDnsEntry.pm +++ b/traffic_ops/app/lib/UI/StaticDnsEntry.pm @@ -164,7 +164,7 @@ sub read { my @data; my $orderby = "deliveryservice"; $orderby = $self->param('orderby') if ( defined $self->param('orderby') ); - my $rs_data = $self->db->resultset("Staticdnsentry")->search( undef, { order_by => $orderby } ); + my $rs_data = $self->db->resultset("Staticdnsentry")->search( undef, { prefetch => [ 'deliveryservice', 'type', 'cachegroup' ], order_by => $orderby } ); while ( my $row = $rs_data->next ) { push( @data, { http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/d1a42eac/traffic_ops/app/lib/UI/Steering.pm ---------------------------------------------------------------------- diff --git a/traffic_ops/app/lib/UI/Steering.pm b/traffic_ops/app/lib/UI/Steering.pm index 8a3cebd..b6ab1ad 100644 --- a/traffic_ops/app/lib/UI/Steering.pm +++ b/traffic_ops/app/lib/UI/Steering.pm @@ -64,7 +64,7 @@ sub get_ds_name { sub get_deliveryservices { my $self = shift; my %ds_data; - my $rs = $self->db->resultset('Deliveryservice'); + my $rs = $self->db->resultset('Deliveryservice')->search(undef, { prefetch => [ 'type' ] }); while ( my $row = $rs->next ) { if ( $row->type->name =~ m/^HTTP/ ) { $ds_data{ $row->id } = $row->xml_id;
