[Bug 15103] New: Import Borrowers Performance Improvement
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 Bug ID: 15103 Summary: Import Borrowers Performance Improvement Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Patrons Assignee: koha-bugs@lists.koha-community.org Reporter: cnorthcott.work@gmail.com QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle.m.hall@gmail.com Blocks: 7923 Import Borrowers has a long run/load time to import batch of borrowers. I have attempted to improve the runtime of the import borrowers tool and my enhancement reduces the time to import by an average of 22%. I discovered the extended runtime was mostly due to the intentionally expensive password hashing algorithm used by Koha which is largely unavoidable, but I was able to save some time by switching calls to DBIx::Class::ResultSet to reduce the import of 5000 patrons from 242.2 seconds to 187.8 seconds, saving almost a whole extra minute (54.4 seconds) of database calls. Referenced Bugs: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7923 [Bug 7923] Performance omnibus -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 --- Comment #1 from cnorthcott.work@gmail.com --- Sorry, looks like I didn't proofread my description very well it should read: I saved time by switching calls from DBIx::Class::ResultSet to basic SQL to $dbh which reduced the import of 5000 patrons from 242.2 seconds to 187.8 seconds, saving almost a whole extra minute (54.4 seconds) of database calls. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 cnorthcott.work@gmail.com changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff Patch complexity|--- |Small patch -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 --- Comment #2 from cnorthcott.work@gmail.com --- Created attachment 44443 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=44443&action=edit Bug 15103: Import Borrowers Performance Improvement Improves the performance of importing borrowers in batches from file by by switching out resultset calls for more direct SQL queries and using hash lookups instead of repeated DB lookups. 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) Download the PatronDataCSV1.csv and PatronDataCSV2.csv attachements from the bug page or organise your own csv's with data for 1000 or more patrons to import. 3) Navigate to Home > Tools > Import Patrons in the Koha Intranet (http://demo-intra.mykoha.co.nz/cgi-bin/koha/tools/import_borrowers.pl) 4) Click the "Browse..." button and select PatronDataCSV1.csv or your equilient. 5) 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. 6) Scroll to the bottom of the page and click the "Import" button (simultaneously start your stopwatch if using one) 7) When the page appears showing completion of the import, stop your stopwatch and check the time or navigate to the folder you have set NYTProf to output. 8) Record the runtime. This is the pre-optimisation time. 9) Apply this patch. 9) Repeat steps 2-7 of this testplan with the patch applied and use the PatronDataCSV2.csv this time around. This will yield the post-optimisation time. 10) Compare the pre-optimisation time and post optimisation time. The second post-optimisation time should be faster. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 --- Comment #3 from cnorthcott.work@gmail.com --- Created attachment 44444 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=44444&action=edit CSV file containing patron data for import -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 --- Comment #4 from cnorthcott.work@gmail.com --- Created attachment 44445 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=44445&action=edit CSV file containing patron data for import -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 rocio@bywatersolutions.com changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |rocio@bywatersolutions.com --- Comment #5 from rocio@bywatersolutions.com --- I tested this on a test site and got these results: csv1 - 53.75 secs csv2 - 48.27 secs I erased all borrowers in the table and tested again: csv1 - 52.92 sec csv2 - 47.74 sec I would recommend that someone else test this to confirm. I used the csv files as-is, with the exception of changing the branch and category codes to match my configuration. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 rocio@bywatersolutions.com changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 rocio@bywatersolutions.com changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #44443|0 |1 is obsolete| | --- Comment #6 from rocio@bywatersolutions.com --- Created attachment 46625 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=46625&action=edit Bug 15103: Import Borrowers Performance Improvement Improves the performance of importing borrowers in batches from file by by switching out resultset calls for more direct SQL queries and using hash lookups instead of repeated DB lookups. 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) Download the PatronDataCSV1.csv and PatronDataCSV2.csv attachements from the bug page or organise your own csv's with data for 1000 or more patrons to import. 3) Navigate to Home > Tools > Import Patrons in the Koha Intranet (http://demo-intra.mykoha.co.nz/cgi-bin/koha/tools/import_borrowers.pl) 4) Click the "Browse..." button and select PatronDataCSV1.csv or your equilient. 5) 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. 6) Scroll to the bottom of the page and click the "Import" button (simultaneously start your stopwatch if using one) 7) When the page appears showing completion of the import, stop your stopwatch and check the time or navigate to the folder you have set NYTProf to output. 8) Record the runtime. This is the pre-optimisation time. 9) Apply this patch. 9) Repeat steps 2-7 of this testplan with the patch applied and use the PatronDataCSV2.csv this time around. This will yield the post-optimisation time. 10) Compare the pre-optimisation time and post optimisation time. The second post-optimisation time should be faster. Signed-off-by: Rocio Jordan <rocio@bywatersolutions.com> -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 --- Comment #7 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Comment on attachment 46625 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=46625 Bug 15103: Import Borrowers Performance Improvement Review of attachment 46625: --> (http://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=15103&attachment=46625) ----------------------------------------------------------------- ::: C4/Members.pm @@ +736,5 @@
$data{'dateenrolled'} = output_pref( { dt => dt_from_string, dateonly => 1, dateformat => 'iso' } ); }
+ # Check the member category privacy level + my $patron_privacy = GetCategoryPrivacy( $data{'categorycode'} );
You are moving backward, we are trying to use DBIx::Class everywhere. The generation of SQL queries should not be done anymore. Moreover bug 15407 is going to remove the patron categories code from C4::Members. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15103 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org Status|Signed Off |Failed QA -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org