From 3aa85f484b5679a1cf75ba558e4af306bdf77ef0 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 26 Nov 2013 07:33:50 -0500 Subject: [PATCH] Bug 11305 - Patrons with hundreds of checkouts cause extreme slowdown - Quick fix [v3.12.x] For pages such as circulation.pl and moremember.pl, any patron with over a hundred checkouts or more can take an extremely long time to load ( 10's of seconds or more ). This is due to the large amount of data pulled and processed to display the patron's previous checkouts, relative's checkouts, and holds. To use: 1) Apply this patch 2) Create a "Local use" system preference "CirculationRowsLimit" 3) Set CirculationRowsLimit to an integer value. This value will be the maximum number of rows pulled for issues, relatives' issues and holds for circulation.pl. To view a patron's entire list of issues, use moremember.pl --- C4/ILSDI/Services.pm | 3 +- C4/Members.pm | 10 ++++-- C4/Reserves.pm | 38 ++++++++++--------- C4/SIP/ILS/Patron.pm | 3 +- circ/circulation.pl | 8 +++-- .../prog/en/modules/circ/circulation.tt | 7 ++-- members/deletemem.pl | 2 +- members/moremember.pl | 4 +- opac/opac-ics.pl | 2 +- opac/opac-user.pl | 2 +- opac/sco/sco-main.pl | 2 +- 11 files changed, 46 insertions(+), 35 deletions(-) diff --git a/C4/ILSDI/Services.pm b/C4/ILSDI/Services.pm index 81a03fe..fafa623 100644 --- a/C4/ILSDI/Services.pm +++ b/C4/ILSDI/Services.pm @@ -424,7 +424,8 @@ sub GetPatronInfo { # Issues management if ( $cgi->param('show_loans') eq "1" ) { - my $issues = GetPendingIssues($borrowernumber); + my $issues = + GetPendingIssues( { borrowernumbers => [$borrowernumber] } ); foreach my $issue ( @$issues ){ $issue->{'issuedate'} = $issue->{'issuedate'}->strftime('%Y-%m-%d %H:%M'); $issue->{'date_due'} = $issue->{'date_due'}->strftime('%Y-%m-%d %H:%M'); diff --git a/C4/Members.pm b/C4/Members.pm index 24979f8..7a55ff5 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -1001,7 +1001,7 @@ sub UpdateGuarantees { } =head2 GetPendingIssues - my $issues = &GetPendingIssues(@borrowernumber); + my $issues = &GetPendingIssues({ borrowernumbers => \@borrowernumber [, limit => $limit ] }); Looks up what the patron with the given borrowernumber has borrowed. @@ -1014,7 +1014,10 @@ The keys include C fields except marc and marcxml. #' sub GetPendingIssues { - my @borrowernumbers = @_; + my ( $params ) = @_; + + my @borrowernumbers = @{ $params->{borrowernumbers} }; + my $limit = $params->{limit}; unless (@borrowernumbers ) { # return a ref_to_array return \@borrowernumbers; # to not cause surprise to caller @@ -1063,7 +1066,8 @@ sub GetPendingIssues { LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber WHERE $bquery - ORDER BY issues.issuedate" + ORDER BY issues.issuedate DESC, issues.timestamp DESC + LIMIT $limit" ; my $sth = C4::Context->dbh->prepare($query); diff --git a/C4/Reserves.pm b/C4/Reserves.pm index 7273802..62a9f7d 100644 --- a/C4/Reserves.pm +++ b/C4/Reserves.pm @@ -362,27 +362,29 @@ TODO :: Descritpion =cut sub GetReservesFromBorrowernumber { - my ( $borrowernumber, $status ) = @_; + my ( $borrowernumber, $status, $count ) = @_; my $dbh = C4::Context->dbh; my $sth; - if ($status) { - $sth = $dbh->prepare(" - SELECT * - FROM reserves - WHERE borrowernumber=? - AND found =? - ORDER BY reservedate - "); - $sth->execute($borrowernumber,$status); - } else { - $sth = $dbh->prepare(" - SELECT * - FROM reserves - WHERE borrowernumber=? - ORDER BY reservedate - "); - $sth->execute($borrowernumber); + my @params; + + my $sql = " + SELECT * + FROM reserves + WHERE borrowernumber=? + "; + push( @params, $borrowernumber ); + + if ( $status ) { + $sql .= " AND found = ? "; + push( @params, $status ); } + + $sql .= " ORDER BY reservedate "; + $sql .- "LIMIT $count" if $count; + + $sth = $dbh->prepare( $sql ); + $sth->execute( @params ); + my $data = $sth->fetchall_arrayref({}); return @$data; } diff --git a/C4/SIP/ILS/Patron.pm b/C4/SIP/ILS/Patron.pm index ac24d2f..15010a8 100644 --- a/C4/SIP/ILS/Patron.pm +++ b/C4/SIP/ILS/Patron.pm @@ -116,7 +116,8 @@ sub new { # FIXME: populate fine_items recall_items # $ilspatron{hold_items} = (GetReservesFromBorrowernumber($kp->{borrowernumber},'F')); $ilspatron{unavail_holds} = [(GetReservesFromBorrowernumber($kp->{borrowernumber}))]; - $ilspatron{items} = GetPendingIssues($kp->{borrowernumber}); + $ilspatron{items} = + GetPendingIssues( { borrowernumbers => [ $kp->{borrowernumber} ] } ); $self = \%ilspatron; $debug and warn Dumper($self); syslog("LOG_DEBUG", "new ILS::Patron(%s): found patron '%s'", $patron_id,$self->{id}); diff --git a/circ/circulation.pl b/circ/circulation.pl index d0eed83..96e8f0d 100755 --- a/circ/circulation.pl +++ b/circ/circulation.pl @@ -342,7 +342,7 @@ if ($borrowernumber) { # new op dev # now we show the status of the borrower's reservations - my @borrowerreserv = GetReservesFromBorrowernumber($borrowernumber ); + my @borrowerreserv = GetReservesFromBorrowernumber($borrowernumber, undef, C4::Context->preference('CirculationRowsLimit') ); my @reservloop; my @WaitingReserveLoop; @@ -493,10 +493,12 @@ if ($borrower) { #push @borrowernumbers, $borrower->{'borrowernumber'}; # get each issue of the borrower & separate them in todayissues & previous issues - my $issueslist = GetPendingIssues($borrower->{'borrowernumber'}); + my $issueslist = GetPendingIssues( + { borrowernumbers => [ $borrower->{'borrowernumber'} ], limit => C4::Context->preference('CirculationRowsLimit') } ); my $relissueslist = []; if ( @relborrowernumbers ) { - $relissueslist = GetPendingIssues(@relborrowernumbers); + $relissueslist = + GetPendingIssues( { borrowernumbers => \@relborrowernumbers , limit => C4::Context->preference('CirculationRowsLimit') } ); } build_issue_data($issueslist, 0); diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt index a389f2f..194fe42 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt @@ -1,3 +1,4 @@ +[% USE Koha %] [% USE KohaBranchName %] [% USE KohaDates %] [% IF ( export_remove_fields OR export_with_csv_profile ) %] @@ -769,15 +770,15 @@ No patron matched [% message %]