Bug 29702 - all_libraries routine in library groups make a DB call per member of group
Summary: all_libraries routine in library groups make a DB call per member of group
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Nick Clemens
QA Contact: Tomás Cohen Arazi
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-12-15 15:44 UTC by Nick Clemens
Modified: 2022-12-12 21:24 UTC (History)
5 users (show)

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


Attachments
Bug 29702: fetch group libraries in a single call (1.77 KB, patch)
2021-12-15 15:47 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 29702: fetch group libraries in a single call (1.84 KB, patch)
2021-12-21 15:23 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 29702: Fetch group libraries in a single call (1.89 KB, patch)
2022-01-06 18:55 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2021-12-15 15:44:03 UTC
The current code gets all group members, then loops through and fetches the library if there is a branchcode, or recursivley calls itself if a group. This slows down performance

To recreate:
1 - Add 100 items to a biblio
2 - Define a library group as a hold group
3 - Add all libraries to the group
4 - Set 'Default checkout, hold and return policy'->'Hold pickup library match' = 'Any library'
5 - place a hold on the record and note load time after patron is selected
6 - Set 'Default checkout, hold and return policy'->'Hold pickup library match' = 'Patrons hold group'
7 - place a hold, note longer load time after patron selection
Comment 1 Nick Clemens 2021-12-15 15:47:24 UTC
Created attachment 128572 [details] [review]
Bug 29702: fetch group libraries in a single call

The current code gets all group members, then loops through and fetches the library if there is a
branchcode, or recursively calls itself if a group. This slows down performance.

We can utilize the 'libraries' method to get all child libraries at once, then make a check
for child groups separately

To recreate:
 1 - Add 100 items to a biblio
 2 - Define a library group as a hold group
 3 - Add all libraries to the group
 4 - Set 'Default checkout, hold and return policy'->'Hold pickup library match' = 'Any library'
 5 - place a hold on the record and note load time after patron is selected
 6 - Set 'Default checkout, hold and return policy'->'Hold pickup library match' = 'Patrons hold group'
 7 - place a hold, note longer load time after patron selection
 8 - Apply patch
 9 - note improvement
10 - prove -v t/db_dependent/Koha/Libraries.t
Comment 2 Andrew Fuerste-Henry 2021-12-21 15:23:08 UTC
Created attachment 128843 [details] [review]
Bug 29702: fetch group libraries in a single call

The current code gets all group members, then loops through and fetches the library if there is a
branchcode, or recursively calls itself if a group. This slows down performance.

We can utilize the 'libraries' method to get all child libraries at once, then make a check
for child groups separately

To recreate:
 1 - Add 100 items to a biblio
 2 - Define a library group as a hold group
 3 - Add all libraries to the group
 4 - Set 'Default checkout, hold and return policy'->'Hold pickup library match' = 'Any library'
 5 - place a hold on the record and note load time after patron is selected
 6 - Set 'Default checkout, hold and return policy'->'Hold pickup library match' = 'Patrons hold group'
 7 - place a hold, note longer load time after patron selection
 8 - Apply patch
 9 - note improvement
10 - prove -v t/db_dependent/Koha/Libraries.t

Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
Comment 3 Tomás Cohen Arazi 2022-01-06 18:55:52 UTC
Created attachment 129116 [details] [review]
Bug 29702: Fetch group libraries in a single call

The current code gets all group members, then loops through and fetches the library if there is a
branchcode, or recursively calls itself if a group. This slows down performance.

We can utilize the 'libraries' method to get all child libraries at once, then make a check
for child groups separately

To recreate:
 1 - Add 100 items to a biblio
 2 - Define a library group as a hold group
 3 - Add all libraries to the group
 4 - Set 'Default checkout, hold and return policy'->'Hold pickup library match' = 'Any library'
 5 - place a hold on the record and note load time after patron is selected
 6 - Set 'Default checkout, hold and return policy'->'Hold pickup library match' = 'Patrons hold group'
 7 - place a hold, note longer load time after patron selection
 8 - Apply patch
 9 - note improvement
10 - prove -v t/db_dependent/Koha/Libraries.t

Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 4 Tomás Cohen Arazi 2022-01-06 19:21:27 UTC
We could try using closure tables to optimize even further.
Comment 5 Fridolin Somers 2022-01-14 08:12:44 UTC
Pushed to master for 22.05, thanks to everybody involved 🦄
Comment 6 Kyle M Hall 2022-01-14 14:40:44 UTC
Pushed to 21.11.x for 21.11.02
Comment 7 Andrew Fuerste-Henry 2022-01-25 18:06:26 UTC
Pushed to 21.05.x for 21.05.09
Comment 8 Victor Grousset/tuxayo 2022-02-11 22:07:27 UTC
Not backported to oldoldstable (20.11.x). Feel free to ask if it's needed.