Bug 9207

Summary: Improve z39.50 speed slowdowns for sites with many records
Product: Koha Reporter: Kyle M Hall <kyle>
Component: CatalogingAssignee: Kyle M Hall <kyle>
Status: CLOSED FIXED QA Contact: Marcel de Rooy <m.de.rooy>
Severity: minor    
Priority: P5 - low CC: chris, m.de.rooy
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:
Attachments: Bug 9207 - Improve z39.50 speed slowdowns for sites with many records
Bug 9207 - Improve z39.50 speed slowdowns for sites with many records

Description Kyle M Hall 2012-12-04 13:47:44 UTC
Sites with very large numbers of imported records may experience increasingly slow z39.50 searches. This is cause by the triggering of the subroutine _update_batch_record_counts for each and every result of a z39.50 search. Fixing this will be very difficult though.

However, I have discovered a much simpler solution that, while not as good as bypassing _update_batch_record_counts, still gives a dramatic boost to speed, adding an index to import_records for import_batch_id/record_type

ALTER TABLE  import_records ADD INDEX batch_id__record_type ( 
import_batch_id, record_type )
Comment 1 Kyle M Hall 2012-12-04 13:51:36 UTC Comment hidden (obsolete)
Comment 2 Jared Camins-Esakov 2012-12-04 14:04:08 UTC
Depending on the number of servers that are being searched, there may be a lesser slowdown caused by ZOOM not releasing the resultsets and connections after it's done. Whether this is making a difference in your particular case, of course, I couldn't say.
Comment 3 Marcel de Rooy 2013-03-08 08:47:14 UTC
Created attachment 15967 [details] [review]
Bug 9207 - Improve z39.50 speed slowdowns for sites with many records

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 4 Marcel de Rooy 2013-03-08 08:52:35 UTC
I tested the new index, but it is hard to say if it is really faster with just a few thousand records in import_records.
I have timed with Time::HiRes a few times, and got slightly better results. But just running the queries like _update_batch_record_counts does in mysql did not really make a difference.
Nevertheless, I can imagine that this index makes a difference when you have much more records.
Tested the upgrade.
Tested the install by running kohastructure partially from command-line.

Amended the patch for a small detail: there is no index in Koha with two underscores in the name. So I removed one underscore.

No further QA comments: Passed QA
Comment 5 Jared Camins-Esakov 2013-03-11 12:57:43 UTC
This patch has been pushed to master.
Comment 6 Chris Cormack 2013-03-16 21:04:11 UTC
Pushed to 3.10.x and 3.8.x will be in 3.10.4 and 3.8.11