[Bug 42878] New: Batch the available-item check in ES marc_records_to_documents
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 Bug ID: 42878 Summary: Batch the available-item check in ES marc_records_to_documents Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Searching - Elasticsearch Assignee: koha-bugs@lists.koha-community.org Reporter: martin.renvoize@openfifth.co.uk QA Contact: testopia@bugs.koha-community.org Target Milestone: --- When building Elasticsearch documents, marc_records_to_documents runs one query per record to set the 'available' flag: SELECT COUNT(*) FROM items WHERE biblionumber=? AND onloan IS NULL AND itemlost=0 (Koha/SearchEngine/Elasticsearch.pm, the "at least one available item" block). marc_records_to_documents is normally given a whole batch of records, so this is N queries per batch. Availability for every biblionumber in the batch could be fetched with a single grouped query instead. This matters most on a networked database, where it is one extra round-trip per record during reindex. Test plan: 1. prove t/db_dependent/Koha/SearchEngine/Elasticsearch.t 2. Confirm the 'available' flag is unchanged for records with/without available items. 3. Count DB statements during a batch reindex before/after. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42881 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42881 [Bug 42881] Improve Elasticsearch Indexing performance -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |martin.renvoize@openfifth.c |ity.org |o.uk Status|NEW |ASSIGNED -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff Patch complexity|--- |Small patch QA Contact|testopia@bugs.koha-communit |nick@bywatersolutions.com |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 --- Comment #1 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 200641 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=200641&action=edit Bug 42878: Batch the available-item check in ES marc_records_to_documents Previously, the marc_records_to_documents method performed a per-record COUNT query to check if a biblionumber had any available items. For a batch of N records, this resulted in N separate database queries: SELECT COUNT(*) FROM items WHERE biblionumber=? AND onloan IS NULL AND itemlost=0 This commit optimizes the approach by: 1. Before the per-record loop (for BIBLIOS index only), collecting all biblionumbers from the batch of records. 2. Running a single grouped query to fetch biblionumbers that have at least one available item: SELECT DISTINCT biblionumber FROM items WHERE biblionumber IN (...) AND onloan IS NULL AND itemlost=0 3. Building a hash %has_available mapping biblionumber => 1 for fast lookups. 4. In the per-record loop, replacing the per-record COUNT query with a simple hash lookup. The semantics are preserved identically: - Records with no biblionumber field (missing or empty) do not get an 'available' key, exactly as before. - Records with a biblionumber but no available items get available => \0. - Records with at least one available item get available => \1. For large batches, this can reduce database queries from N to 1. Test plan: Run the existing Elasticsearch tests to verify all available-item tests pass. The new batch test verifies that multiple records in one call are correctly evaluated for availability. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org