https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30004 --- Comment #1 from Julian Maurice <julian.maurice@biblibre.com> --- Created attachment 130106 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=130106&action=edit Bug 30004: Prevent TooMany from executing too many SQL queries If a maximum number of checkouts allowed is defined in circulation rules, C4::Circulation::TooMany will loop over all patron's checkouts. When a patron has several hundreds of checkouts, it can really slow down the checkout process by several seconds (or even tens of seconds) This patch does two things: - Always prefetch item data so that `$c->item` does not execute an additional SQL query at every iteration of the loop. Item data is always needed at the first line of the loop, so there is really no downside for doing this. - Build the `@types` array only once, out of the checkouts loop. Since it does not depend at all on patron's checkouts data, it does not make sense to build it inside the loop. Test plan: 1. Before applying the patch, create a patron with a lot of checkouts. I tested with 1000 checkouts, but the slowness should be noticeable with less. 2. Make sure you have a circulation rule (one that apply to your patron and the item(s) you will check out for testing) with a maximum number of checkouts allowed 3. Check out an item for the patron with a lot of checkouts. Measure the time it takes. 4. Apply the patch 5. Check out another item (or check in and then check out the same item used in step 3). Measure the time it takes and compare it to step 3. It should be faster now. 6. Run `prove t/db_dependent/Circulation/TooMany.t` -- 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=30004 --- Comment #2 from Julian Maurice <julian.maurice@biblibre.com> --- This problem affects at least versions 20.11, 21.05, 21.11, and master -- 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=30004 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m Severity|enhancement |major Blocks| |7923 --- Comment #3 from Fridolin Somers <fridolin.somers@biblibre.com> --- Performance issue so i change from enhancement to major bug Good catch Julian ;) Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7923 [Bug 7923] Performance omnibus -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30004 --- Comment #5 from Julian Maurice <julian.maurice@biblibre.com> --- (In reply to David Nind from comment #4)

Is there an easy way to generate 1,000 checkouts for a patron?

You can create multiple items at once by clicking on "Add multiple copies" button when creating an item. For checkouts, there is the "batch check out" feature. (Maybe do not create 1000 checkouts at once as it can take a lot of time, but by batches of 100 it should be doable) -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30004 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #130106|0 |1 is obsolete| | --- Comment #6 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 131066 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=131066&action=edit Bug 30004: Prevent TooMany from executing too many SQL queries If a maximum number of checkouts allowed is defined in circulation rules, C4::Circulation::TooMany will loop over all patron's checkouts. When a patron has several hundreds of checkouts, it can really slow down the checkout process by several seconds (or even tens of seconds) This patch does two things: - Always prefetch item data so that `$c->item` does not execute an additional SQL query at every iteration of the loop. Item data is always needed at the first line of the loop, so there is really no downside for doing this. - Build the `@types` array only once, out of the checkouts loop. Since it does not depend at all on patron's checkouts data, it does not make sense to build it inside the loop. Test plan: 1. Before applying the patch, create a patron with a lot of checkouts. I tested with 1000 checkouts, but the slowness should be noticeable with less. 2. Make sure you have a circulation rule (one that apply to your patron and the item(s) you will check out for testing) with a maximum number of checkouts allowed 3. Check out an item for the patron with a lot of checkouts. Measure the time it takes. 4. Apply the patch 5. Check out another item (or check in and then check out the same item used in step 3). Measure the time it takes and compare it to step 3. It should be faster now. 6. Run `prove t/db_dependent/Circulation/TooMany.t` Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30004 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart+koha@gmail. | |com --- Comment #7 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Compared a call to CanBookBeIssued in a pl script, with 960 checkouts: Without patch: 4.3s With patch : 1.7s -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30004 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #131066|0 |1 is obsolete| | --- Comment #8 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 131972 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=131972&action=edit Bug 30004: Prevent TooMany from executing too many SQL queries If a maximum number of checkouts allowed is defined in circulation rules, C4::Circulation::TooMany will loop over all patron's checkouts. When a patron has several hundreds of checkouts, it can really slow down the checkout process by several seconds (or even tens of seconds) This patch does two things: - Always prefetch item data so that `$c->item` does not execute an additional SQL query at every iteration of the loop. Item data is always needed at the first line of the loop, so there is really no downside for doing this. - Build the `@types` array only once, out of the checkouts loop. Since it does not depend at all on patron's checkouts data, it does not make sense to build it inside the loop. Test plan: 1. Before applying the patch, create a patron with a lot of checkouts. I tested with 1000 checkouts, but the slowness should be noticeable with less. 2. Make sure you have a circulation rule (one that apply to your patron and the item(s) you will check out for testing) with a maximum number of checkouts allowed 3. Check out an item for the patron with a lot of checkouts. Measure the time it takes. 4. Apply the patch 5. Check out another item (or check in and then check out the same item used in step 3). Measure the time it takes and compare it to step 3. It should be faster now. 6. Run `prove t/db_dependent/Circulation/TooMany.t` Signed-off-by: Nick Clemens <nick@bywatersolutions.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=30004 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Small patch -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30004 --- Comment #10 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to master for 22.05, thanks to everybody involved 🦄 -- You are receiving this mail because: You are watching all bug changes.