Bug 16855

Summary: Poor performance due to high overhead of SQL call in export.pl
Product: Koha Reporter: Nick Clemens <nick>
Component: ToolsAssignee: Nick Clemens <nick>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: f.demians, jonathan.druart, julian.maurice, kyle
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on: 14722    
Bug Blocks:    
Attachments: Bug 16855 - Poor performance due to high overhead of SQL call in export.pl
[SIGNED-OFF] Bug 16855 - Poor performance due to high overhead of SQL call in export.pl
Bug 16855 - Poor performance due to high overhead of SQL call in export.pl

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