From f7dd75d54bd12a68aa455cdeb998725abcc85eb1 Mon Sep 17 00:00:00 2001 From: Catherine Date: Tue, 3 Nov 2015 03:36:44 +0000 Subject: [PATCH] Bug 15107: Batch Patron Deletion (Trash) Performance Improvement Batch Patron Modification has a fairly long run/load time. I have attempted to improve the runtime of the Batch Patron Modification tool by switching out more expensive resultset calls for faster more direct SQL queries. Test Plan: 1) Prepare NTYprof (http://wiki.koha-community.org/wiki/Profiling_with_Devel::NYTProf) or procure a stopwatch (stopwatch will be simpler but less accurate). 2) Ensure you have a decent number of patrons in your Koha system (>500) if you are lacking patrons to modify in your database you can import 1000 using the PatronDataCSV.csv attachement and the Import Patrons tool before moving to step 3. 4) Navigate to Home > Tools > Batch Patrons Deletion in the Koha Intranet 5) Check the box beside the "Verify you want to delete patrons" box 6) Under "who have not borrowed since" enter todays date 7) You should get a message saying: "1000 patrons will be deleted 0 checkout history will be anonymized" If not please go back to step 2 and import patrons using the PatronDataCSV.csv 8) Select the radio button option beside "Move these patrons to the trash" 9) If you are using a stopwatch, prepare your stopwatch so that you will start counting seconds from the point you click the button in the next step. 10) Click the "Finsh" button (simultaneously start your stopwatch if using one) 11) When the page appears showing completion of the change, stop your stopwatch and check the time or navigate to the folder you have set NYTProf to output. 12) Record the runtime. This is the pre-optimisation time. 13) Apply this patch. 14) Repeat steps 2-12 of this testplan with the patch applied. This will yield the post-optimisation time. 15) Compare the pre-optimisation time and post optimisation time. The second post-optimisation time should be faster. --- C4/Members.pm | 40 ++++++++++++++++++++++++++++++---------- 1 file changed, 30 insertions(+), 10 deletions(-) diff --git a/C4/Members.pm b/C4/Members.pm index 5fb1764..d112031 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -456,9 +456,10 @@ sub GetMember { } } $debug && warn $select, " ",values %information; - my $sth = $dbh->prepare("$select"); + my $sth = $dbh->prepare_cached("$select"); $sth->execute(map{$information{$_}} keys %information); my $data = $sth->fetchall_arrayref({}); + $sth->finish; #FIXME interface to this routine now allows generation of a result set #so whole array should be returned but bowhere in the current code expects this if (@{$data} ) { @@ -1771,17 +1772,36 @@ The routine returns 1 for success, undef for failure. =cut sub MoveMemberToDeleted { - my ($member) = shift or return; - - my $schema = Koha::Database->new()->schema(); - my $borrowers_rs = $schema->resultset('Borrower'); - $borrowers_rs->result_class('DBIx::Class::ResultClass::HashRefInflator'); - my $borrower = $borrowers_rs->find($member); - return unless $borrower; + my ($borrowernumber) = shift or return; + my $dbh = C4::Context->dbh; + my $borrower_columns = q{ + title, surname, firstname, dateofbirth, initials, + othernames, sex, relationship, guarantorid, streetnumber, + streettype, address, address2, city, state, zipcode, + country, phone, phonepro, mobile, email, emailpro, fax, + B_streetnumber, B_streettype, B_address, B_address2, + B_city, B_state, B_zipcode, B_country, B_phone, B_email, + contactnote, altcontactfirstname, altcontactsurname, + altcontactaddress1, altcontactaddress2, altcontactaddress3, + contactname, contactfirstname, contacttitle, altcontactstate, + altcontactzipcode, altcontactcountry, altcontactphone, + cardnumber, branchcode, categorycode, sort1, sort2, + dateenrolled, dateexpiry, opacnote, borrowernotes, userid, + password, flags, gonenoaddress, lost, debarred, + debarredcomment, smsalertnumber, privacy + }; - my $deleted = $schema->resultset('Deletedborrower')->create($borrower); + my $insert_query = " + INSERT INTO deletedborrowers + ($borrower_columns) + SELECT $borrower_columns + FROM borrowers + WHERE borrowernumber = ? + "; - return $deleted ? 1 : undef; + my $sth = $dbh->prepare($insert_query); + my $result = $sth->execute($borrowernumber); + return $result ? 1 : undef; } =head2 DelMember -- 1.7.10.4