Bug 16855 - Poor performance due to high overhead of SQL call in export.pl
Summary: Poor performance due to high overhead of SQL call in export.pl
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Tools (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Nick Clemens
QA Contact: Testopia
URL:
Keywords:
Depends on: 14722
Blocks:
  Show dependency treegraph
 
Reported: 2016-07-05 18:12 UTC by Nick Clemens
Modified: 2017-12-07 22:17 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 16855 - Poor performance due to high overhead of SQL call in export.pl (1.16 KB, patch)
2016-07-05 18:53 UTC, Nick Clemens
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 16855 - Poor performance due to high overhead of SQL call in export.pl (1.21 KB, patch)
2016-07-06 04:10 UTC, Srdjan Jankovic
Details | Diff | Splinter Review
Bug 16855 - Poor performance due to high overhead of SQL call in export.pl (1.21 KB, patch)
2016-07-06 15:34 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2016-07-05 18:12:51 UTC
Export.pl uses Koha::Biblioitems->search() to retrieve biblionumbers for the export process.

The SQL code generated by this does a full dump of the the biblioitems table including the marcxml for each record.

This table can be quite large and on smaller servers can actually cause debian to fire oom-killer on the process.

As the results of the search are only used to push biblionumbers into an array it might make better sense to do a direct SQL query and not retrieve the marcxml column
Comment 1 Nick Clemens 2016-07-05 18:53:04 UTC Comment hidden (obsolete)
Comment 2 Srdjan Jankovic 2016-07-06 04:10:59 UTC Comment hidden (obsolete)
Comment 3 Jonathan Druart 2016-07-06 15:34:38 UTC
Created attachment 53138 [details] [review]
Bug 16855 - Poor performance due to high overhead of SQL call in export.pl

This patch eliminates all columns retrieved in the biblioitems query
except for biblionumber.

To test:
1 - Go to tools->Export data
2 - Export using various filters and note you get expected records
3 - Apply patch
4 - Ensure exported results match results before patch

Signed-off-by: Srdjan <srdjan@catalyst.net.nz>
Comment 4 Jonathan Druart 2016-07-06 15:36:06 UTC
Quick benchmark using: 

use Koha::Biblioitems;
my @record_ids;
my $biblioitems = Koha::Biblioitems->search( {}, { join => 'items', columns => 'biblionumber' } );
while ( my $biblioitem = $biblioitems->next ) {
    push @record_ids, $biblioitem->biblionumber;
}

and

use Koha::Biblioitems;
my @record_ids;
my $biblioitems = Koha::Biblioitems->search( {}, { join => 'items' } );
while ( my $biblioitem = $biblioitems->next ) {
    push @record_ids, $biblioitem->biblionumber;
}

gives 1.4s vs 1.7s with 8600 items.
Comment 5 Kyle M Hall 2016-07-08 12:56:04 UTC
Pushed to master for Koha 16.11,t thanks Nick!
Comment 6 Frédéric Demians 2016-08-01 13:24:52 UTC
Pushed in 16.05. Will be in 16.05.02.
Comment 7 Julian Maurice 2016-08-16 14:43:49 UTC
Patch pushed to 3.22.x, will be in 3.22.10