Bugzilla – Attachment 23157 Details for
Bug 11305
Patrons with hundreds of checkouts cause extreme slowdown
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 11305 - Patrons with hundreds of checkouts cause extreme slowdown - Quick fix [v3.12.x]
Bug-11305---Patrons-with-hundreds-of-checkouts-cau.patch (text/plain), 10.99 KB, created by
Kyle M Hall (khall)
on 2013-11-26 12:36:56 UTC
(
hide
)
Description:
Bug 11305 - Patrons with hundreds of checkouts cause extreme slowdown - Quick fix [v3.12.x]
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2013-11-26 12:36:56 UTC
Size:
10.99 KB
patch
obsolete
>From 3aa85f484b5679a1cf75ba558e4af306bdf77ef0 Mon Sep 17 00:00:00 2001 >From: Kyle M Hall <kyle@bywatersolutions.com> >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<biblioitems> 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 <span class="ex">[% message %]</span> > > <ul> > <li> [% IF ( issuecount ) %] >- <a href="#checkouts">[% issuecount %] Checkout(s)</a> >+ <a href="#checkouts">[% IF Koha.Preference('CirculationRowsLimit') %] Newest [% Koha.Preference('CirculationRowsLimit') %] of [% END %] [% issuecount %] Checkout(s)</a> > [% ELSE %] > <a href="#checkouts">0 Checkouts</a> > [% END %]</li> > [% IF ( displayrelissues ) %] >-<li><a href="#relissues">Relatives' checkouts</a></li> >+<li><a href="#relissues">[% IF Koha.Preference('CirculationRowsLimit') %] Newest [% Koha.Preference('CirculationRowsLimit') %] [% END %] Relatives' checkouts</a></li> > [% END %] > <li>[% IF ( countreserv ) %] >- <a href="#reserves">[% countreserv %] Hold(s)</a> >+ <a href="#reserves">[% IF Koha.Preference('CirculationRowsLimit') %] Newest [% Koha.Preference('CirculationRowsLimit') %] of [% END %] [% countreserv %] Hold(s)</a> > [% ELSE %] > <a href="#reserves">0 Holds</a> > [% END %]</li> >diff --git a/members/deletemem.pl b/members/deletemem.pl >index 7a78ead..bada38b 100755 >--- a/members/deletemem.pl >+++ b/members/deletemem.pl >@@ -45,7 +45,7 @@ my ($template, $borrowernumber, $cookie) > > #print $input->header; > my $member=$input->param('member'); >-my $issues = GetPendingIssues($member); # FIXME: wasteful call when really, we only want the count >+my $issues = GetPendingIssues( { borrowernumbers => [$member] } ); # FIXME: wasteful call when really, we only want the count > my $countissues = scalar(@$issues); > > my ($bor)=GetMemberDetails($member,''); >diff --git a/members/moremember.pl b/members/moremember.pl >index 752bb1e..e0d136f 100755 >--- a/members/moremember.pl >+++ b/members/moremember.pl >@@ -239,10 +239,10 @@ if ( C4::Context->preference('OPACPrivacy') ) { > # current issues > # > my @borrowernumbers = GetMemberRelatives($borrowernumber); >-my $issue = GetPendingIssues($borrowernumber); >+my $issue = GetPendingIssues( { borrowernumbers => [$borrowernumber] } ); > my $relissue = []; > if ( @borrowernumbers ) { >- $relissue = GetPendingIssues(@borrowernumbers); >+ $relissue = GetPendingIssues( { borrowernumbers => \@borrowernumbers } ); > } > my $roaddetails = &GetRoadTypeDetails( $data->{'streettype'} ); > my $today = DateTime->now( time_zone => C4::Context->tz); >diff --git a/opac/opac-ics.pl b/opac/opac-ics.pl >index d9d90a6..40bf8f2 100755 >--- a/opac/opac-ics.pl >+++ b/opac/opac-ics.pl >@@ -56,7 +56,7 @@ my ( $borr ) = GetMemberDetails( $borrowernumber ); > my $calendar = Data::ICal->new(); > > # get issued items .... >-my $issues = GetPendingIssues($borrowernumber); >+my $issues = GetPendingIssues( { borrowernumbers => [$borrowernumber] } ); > > foreach my $issue ( @$issues ) { > my $vevent = Data::ICal::Entry::Event->new(); >diff --git a/opac/opac-user.pl b/opac/opac-user.pl >index e9521e2..1bb85b8 100755 >--- a/opac/opac-user.pl >+++ b/opac/opac-user.pl >@@ -154,7 +154,7 @@ my $overdues_count = 0; > my @overdues; > my @issuedat; > my $itemtypes = GetItemTypes(); >-my $issues = GetPendingIssues($borrowernumber); >+my $issues = GetPendingIssues( { borrowernumbers => [$borrowernumber] } ); > if ($issues){ > foreach my $issue ( sort { $b->{date_due}->datetime() cmp $a->{date_due}->datetime() } @{$issues} ) { > # check for reserves >diff --git a/opac/sco/sco-main.pl b/opac/sco/sco-main.pl >index 0f16c8c..9b99bbe 100755 >--- a/opac/sco/sco-main.pl >+++ b/opac/sco/sco-main.pl >@@ -216,7 +216,7 @@ if ($borrower->{cardnumber}) { > # warn "user's branchcode: " . $borrower->{branchcode}; > my $borrowername = sprintf "%s %s", ($borrower->{firstname} || ''), ($borrower->{surname} || ''); > my @issues; >- my ($issueslist) = GetPendingIssues( $borrower->{'borrowernumber'} ); >+ my ($issueslist) = GetPendingIssues( { borrowernumbers => [$borrower->{'borrowernumber'}] } ); > foreach my $it (@$issueslist) { > my ($renewokay, $renewerror) = CanBookBeIssued( > $borrower, >-- >1.7.2.5
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 11305
: 23157