Bug 6946 - Import Batches _update_batch_record_counts has inefficient SQL
Summary: Import Batches _update_batch_record_counts has inefficient SQL
Status: CLOSED INVALID
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: 3.6
Hardware: All All
: P5 - low minor (vote)
Assignee: Ian Walls
QA Contact: Bugs List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-09-30 02:26 UTC by Ian Walls
Modified: 2015-12-03 22:00 UTC (History)
2 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.