Bug 6946

Summary: Import Batches _update_batch_record_counts has inefficient SQL
Product: Koha Reporter: Ian Walls <koha.sekjal>
Component: Architecture, internals, and plumbingAssignee: Ian Walls <koha.sekjal>
Status: CLOSED INVALID QA Contact: Bugs List <koha-bugs>
Severity: minor    
Priority: P5 - low CC: chris
Version: 3.6   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:

Description Ian Walls 2011-09-30 02:26:05 UTC
The queries to update the number of biblios and the number of items for any given import batch are much slower than they need to be.

UPDATE import_batches 
SET num_biblios = (SELECT COUNT(*)
                   FROM import_records
                   WHERE import_batch_id = import_batches.import_batch_id
                   AND record_type = 'biblio')
WHERE import_batch_id = ?

UPDATE import_batches 
SET num_items = (SELECT COUNT(*)
                 FROM import_records
                 JOIN import_items USING (import_record_id)
                 WHERE import_batch_id = import_batches.import_batch_id
                 AND record_type = 'biblio')
WHERE import_batch_id = ?")

The "AND record_type = 'biblio'" lines are unnecesary; every record within the same import_batch_id will have the same record_type, and more importantly, 'biblio' is currently the only supported type.

Removing the line changes the return time of the subquery from over 2 seconds down to 0.01 seconds (on my machine with 34944 items in a batch).  I think this is going to be faster than adding an index on record_type.
Comment 1 Paul Poulain 2011-10-25 15:07:32 UTC
bug affected to Koha 3.6. Entries must be attached to rel_3_8 only when the patch is pushed here (and if it's an ENH)
Comment 2 Chris Cormack 2011-10-28 22:19:08 UTC
I will try implementing an index and checking the speed, on the off chance we do get batch import of authorities working in the future
Comment 3 Katrin Fischer 2015-01-06 18:22:08 UTC
As we have authority batch import in current versions, I think the proposed improvement is invalid now.