[Bug 43101] New: search_for_data_inconsistencies.pl creates a query that ORs every biblionumber in the catalog
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 Bug ID: 43101 Summary: search_for_data_inconsistencies.pl creates a query that ORs every biblionumber in the catalog Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: kyle@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org Target Milestone: --- Queries that use OR instead of IN are less efficient and can lead to situations where the query takes a very long time to complete due to row level scanning. We found one instance where they query had been running for over 10 days! -- 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=43101 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- 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=43101 --- Comment #1 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 202106 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=202106&action=edit Bug 43101: Add unit tests Patch from commit c6379d5 -- 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=43101 --- Comment #2 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 202107 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=202107&action=edit Bug 43101: search_for_data_inconsistencies.pl inlines every biblionumber in the catalog as an OR chain Queries that use OR instead of IN are less efficient and can lead to situations where the query takes a very long time to complete due to row level scanning. We found one instance where they query had been running for over 10 days! This patch makes ids() return a subquery instead of a list of results, and switches the callers to using IN. The database now resolves the set itself and the queries stay a constant size no matter how large the catalog is. This is super efficient because the subquery doesn't even execute as a separate query since biblionumber is the primary key! Test Plan: 1) Apply the unit tests patch 2) prove t/db_dependent/Koha/Database/DataInconsistency.t 3) Note the ids() subtest fails 4) Apply this patch 5) prove t/db_dependent/Koha/Database/DataInconsistency.t 6) Note all tests pass! 7) Run misc/maintenance/search_for_data_inconsistencies.pl 8) Note the output is the same as before the patch 9) Bonus: Re-run it with DBIC_TRACE=1 and note the queries now use IN (SELECT ...) instead of one OR clause per bib! -- 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=43101 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |kyle@bywatersolutions.com |ity.org | -- 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=43101 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=43102 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=43103 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #202106|0 |1 is obsolete| | Attachment #202107|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 --- Comment #3 from David Nind <david@davidnind.com> --- Created attachment 202116 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=202116&action=edit Bug 43101: Add unit tests Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 --- Comment #4 from David Nind <david@davidnind.com> --- Created attachment 202117 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=202117&action=edit Bug 43101: search_for_data_inconsistencies.pl inlines every biblionumber in the catalog as an OR chain Queries that use OR instead of IN are less efficient and can lead to situations where the query takes a very long time to complete due to row level scanning. We found one instance where they query had been running for over 10 days! This patch makes ids() return a subquery instead of a list of results, and switches the callers to using IN. The database now resolves the set itself and the queries stay a constant size no matter how large the catalog is. This is super efficient because the subquery doesn't even execute as a separate query since biblionumber is the primary key! Test Plan: 1) Apply the unit tests patch 2) prove t/db_dependent/Koha/Database/DataInconsistency.t 3) Note the ids() subtest fails 4) Apply this patch 5) prove t/db_dependent/Koha/Database/DataInconsistency.t 6) Note all tests pass! 7) Run misc/maintenance/search_for_data_inconsistencies.pl 8) Note the output is the same as before the patch 9) Bonus: Re-run it with DBIC_TRACE=1 and note the queries now use IN (SELECT ...) instead of one OR clause per bib! Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com Text to go in the| |This fixes the search for release notes| |data inconsistencies script | |(misc/maintenance/search_fo | |r_data_inconsistencies.pl) | |so that it now runs | |significantly faster - it | |replaces OR with IN for | |subqueries. | | | |Technical details: | | | |1. Queries that use OR | |instead of IN are less | |efficient and can lead to | |situations where the query | |takes a very long time to | |complete due to row level | |scanning. Example: We found | |one instance where they | |query had been running for | |over 10 days! | | | |2. This fix makes ids() | |return a subquery instead | |of a list of results, and | |switches the callers to | |using IN. The database now | |resolves the set itself and | |the queries stay a constant | |size no matter how large | |the catalog is. This is | |super efficient because the | |subquery doesn't even | |execute as a separate query | |since biblionumber is the | |primary key! --- Comment #5 from David Nind <david@davidnind.com> --- Testing notes (using KTD): 1. Step 9: DBIC_TRACE=1 perl misc/maintenance/search_for_data_inconsistencies.pl 2. Script runs significantly faster after the patches (noticeable even on the small set of sample data in KTD)! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |jonathan.druart@gmail.com |y.org | CC| |jonathan.druart@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #202116|0 |1 is obsolete| | Attachment #202117|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 --- Comment #6 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 202378 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=202378&action=edit Bug 43101: Add unit tests Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 --- Comment #7 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 202379 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=202379&action=edit Bug 43101: search_for_data_inconsistencies.pl inlines every biblionumber in the catalog as an OR chain Queries that use OR instead of IN are less efficient and can lead to situations where the query takes a very long time to complete due to row level scanning. We found one instance where they query had been running for over 10 days! This patch makes ids() return a subquery instead of a list of results, and switches the callers to using IN. The database now resolves the set itself and the queries stay a constant size no matter how large the catalog is. This is super efficient because the subquery doesn't even execute as a separate query since biblionumber is the primary key! Test Plan: 1) Apply the unit tests patch 2) prove t/db_dependent/Koha/Database/DataInconsistency.t 3) Note the ids() subtest fails 4) Apply this patch 5) prove t/db_dependent/Koha/Database/DataInconsistency.t 6) Note all tests pass! 7) Run misc/maintenance/search_for_data_inconsistencies.pl 8) Note the output is the same as before the patch 9) Bonus: Re-run it with DBIC_TRACE=1 and note the queries now use IN (SELECT ...) instead of one OR clause per bib! Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |26.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 --- Comment #8 from Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> --- Thanks everyone! Pushed to main for 26.11! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|26.11.00 |26.11.00,26.05.03 released in| | Status|Pushed to main |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 --- Comment #9 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to 26.05.x for the upcoming 26.05.03 release. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43101 Baptiste Wojtkowski (bwoj) <baptiste.wojtkowski@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |Pushed to oldstable CC| |baptiste.wojtkowski@biblibr | |e.com Version(s)|26.11.00,26.05.03 |26.11.00,26.05.03,25.11.10 released in| | --- Comment #10 from Baptiste Wojtkowski (bwoj) <baptiste.wojtkowski@biblibre.com> --- Pushed into 25.11.x for 25.11.10 nice work everyone -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org