Bug 41343 - Overdue report is too intensive on systems with many overdues
Summary: Overdue report is too intensive on systems with many overdues
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Circulation (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal
Assignee: Nick Clemens (kidclamp)
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2025-12-01 19:51 UTC by Nick Clemens (kidclamp)
Modified: 2025-12-01 20:23 UTC (History)
2 users (show)

See Also:
GIT URL:
Initiative type: ---
Sponsorship status: ---
Comma delimited list of Sponsors:
Crowdfunding goal: 0
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
Bug 41343: Don't fetch patron objects per overdue (7.01 KB, patch)
2025-12-01 20:19 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 41343: Further improvements (2.88 KB, patch)
2025-12-01 20:19 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens (kidclamp) 2025-12-01 19:51:43 UTC

    
Comment 1 Nick Clemens (kidclamp) 2025-12-01 20:19:53 UTC
Created attachment 190062 [details] [review]
Bug 41343: Don't fetch patron objects per overdue

We use SQL here to get all the needed info - the query says it is slow, but isn't so bad.
Worse is fetching the patron for each borrower with overdues (and more than once if they have several)

It appears we started grabbing the patron so we could use patron title, but we can just pass the info already fetched.

I additionally fetch the patron category types and update patron-title to allow for hide_patron_infos_if_needed to work
without a patron object - we can just use the branchcode as ultimately that's all we need

To test:
1 - Add many items to your catalog (can use my handy repo randitems.pl)
    https://github.com/kidclamp/handy-koha-script
2 - Add many issues (can use randissues.pl from above)
3 - I tested with about 1000
4 - Browse to Circulation->Overdues
5 - Note load time
6 - Apply patch
7 - Restart all
8 - Note load time is faster
Comment 2 Nick Clemens (kidclamp) 2025-12-01 20:19:55 UTC
Created attachment 190063 [details] [review]
Bug 41343: Further improvements

Remove unused patron attribute values sent to the template

Send preferred name

Use SQL to do date comparison to see if an item is overdue
Comment 3 Nick Clemens (kidclamp) 2025-12-01 20:21:58 UTC
For me this went from ~9 seconds to ~6 seconds with 1000 items

Testing the SQL on a system with 100k checkouts, it takes less then 3 seconds, so could do better, but isn't too bad.

We should maybe consider not loading all overdues by default, maybe a syspref to limit, or just default to 1000 items and then allow for loading more? See bug 29098