From 7fd6fd2b21ca0079763698b1323f15971fca7a58 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 7 Sep 2021 12:55:31 -0400 Subject: [PATCH] Bug 28966: Prefetch patron data for holds queue viewer We have a partner processing thousands of holds per day, per branch. At this number of holds, the script loads very slow if it manages to load at all. The primary slowdown is the individual fetch of the patron for each hold to be displayed. If we prefetch those patrons, the entire script should load much faster. Test Plan: 1) Create as many holds as you can ( up to a few thousand if you can ) 2) Run the holds queue viewer, note the load time 3) Apply this patch 4) Restart all the things! 5) Reload the holds queue viewer, load time should be improved 6) Note the HTML generated looks the same --- C4/HoldsQueue.pm | 62 ++++++++++++++++++----------- Koha/Schema/Result/TmpHoldsqueue.pm | 50 ++++++++++++++++++++++- circ/view_holdsqueue.pl | 3 -- 3 files changed, 88 insertions(+), 27 deletions(-) diff --git a/C4/HoldsQueue.pm b/C4/HoldsQueue.pm index 3774214527..1651481eaf 100644 --- a/C4/HoldsQueue.pm +++ b/C4/HoldsQueue.pm @@ -135,35 +135,51 @@ sub GetHoldsQueueItems { my ($branchlimit) = @_; my $dbh = C4::Context->dbh; - my @bind_params = (); - my $query = q/SELECT tmp_holdsqueue.*, biblio.author, items.ccode, items.itype, biblioitems.itemtype, items.location, - items.enumchron, items.cn_sort, biblioitems.publishercode, - biblio.copyrightdate, biblio.subtitle, biblio.medium, - biblio.part_number, biblio.part_name, - biblioitems.publicationyear, biblioitems.pages, biblioitems.size, - biblioitems.isbn, biblioitems.editionstatement, items.copynumber - FROM tmp_holdsqueue - JOIN biblio USING (biblionumber) - LEFT JOIN biblioitems USING (biblionumber) - LEFT JOIN items USING ( itemnumber) - /; + my $search_params; if ($branchlimit) { - $query .=" WHERE tmp_holdsqueue.holdingbranch = ?"; - push @bind_params, $branchlimit; + $search_params->{'me.holdingbranch'} = $branchlimit; } - $query .= " ORDER BY ccode, location, cn_sort, author, title, pickbranch, reservedate"; - my $sth = $dbh->prepare($query); - $sth->execute(@bind_params); + + my $schema = Koha::Database->new->schema; + my $queue_rs = $schema->resultset('TmpHoldsqueue')->search( + $search_params, + { + join => [ qw{ biblio biblioitem borrower item } ], + order_by => [ qw{ ccode location cn_sort author title pickbranch reservedate } ] + } + ); + my $items = []; - while ( my $row = $sth->fetchrow_hashref ){ - # return the bib-level or item-level itype per syspref - if (!C4::Context->preference('item-level_itypes')) { - $row->{itype} = $row->{itemtype}; + while ( my $r = $queue_rs->next ) { + my $result = { $r->get_columns }; + $result->{author} = $r->biblio->author; + $result->{copyrightdate} = $r->biblio->copyrightdate; + $result->{medium} = $r->biblio->medium; + $result->{part_name} = $r->biblio->part_name; + $result->{part_number} = $r->biblio->part_number; + $result->{subittle} = $r->biblio->subtitle; + $result->{editionstatement} = $r->biblioitem->editionstatement; + $result->{isbn} = $r->biblioitem->isbn; + $result->{pages} = $r->biblioitem->pages; + $result->{publicationyear} = $r->biblioitem->publicationyear; + $result->{publishercode} = $r->biblioitem->publishercode; + $result->{size} = $r->biblioitem->size; + $result->{ccode} = $r->item->ccode; + $result->{cn_sort} = $r->item->cn_sort; + $result->{copynumber} = $r->item->copynumber; + $result->{enumchron} = $r->item->enumchron; + $result->{itype} = $r->item->itype; + $result->{location} = $r->item->location; + + if ( !C4::Context->preference('item-level_itypes') ) { + $result->{itype} = $result->{itemtype}; } - delete $row->{itemtype}; - push @$items, $row; + $result->{patron} = Koha::Patron->_new_from_dbic( $r->borrower ); + + push( @$items, $result ); } + return $items; } diff --git a/Koha/Schema/Result/TmpHoldsqueue.pm b/Koha/Schema/Result/TmpHoldsqueue.pm index bb65f43dac..c2d86cec6c 100644 --- a/Koha/Schema/Result/TmpHoldsqueue.pm +++ b/Koha/Schema/Result/TmpHoldsqueue.pm @@ -167,6 +167,54 @@ __PACKAGE__->belongs_to( # Created by DBIx::Class::Schema::Loader v0.07046 @ 2019-05-14 18:14:09 # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:tzgAgf+OVO+IncaTr7SZuQ +__PACKAGE__->belongs_to( + "biblio", + "Koha::Schema::Result::Biblio", + { biblionumber => "biblionumber" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + +__PACKAGE__->belongs_to( + "biblioitem", + "Koha::Schema::Result::Biblioitem", + { biblionumber => "biblionumber" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + +__PACKAGE__->belongs_to( + "borrower", + "Koha::Schema::Result::Borrower", + { borrowernumber => "borrowernumber" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + +__PACKAGE__->belongs_to( + "item", + "Koha::Schema::Result::Item", + { itemnumber => "itemnumber" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + + -# You can replace this text with custom content, and it will be preserved on regeneration 1; diff --git a/circ/view_holdsqueue.pl b/circ/view_holdsqueue.pl index 4e6384e317..11304c1dc6 100755 --- a/circ/view_holdsqueue.pl +++ b/circ/view_holdsqueue.pl @@ -51,9 +51,6 @@ my $itemtypeslimit = $params->{'itemtypeslimit'}; if ( $run_report ) { # XXX GetHoldsQueueItems() does not support $itemtypeslimit! my $items = GetHoldsQueueItems($branchlimit, $itemtypeslimit); - for my $item ( @$items ) { - $item->{patron} = Koha::Patrons->find( $item->{borrowernumber} ); - } $template->param( branchlimit => $branchlimit, total => scalar @$items, -- 2.30.1 (Apple Git-130)