Bugzilla – Attachment 192232 Details for
Bug 23260
Anonymize (remove) patron data from items_last_borrower
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 23260: (QA follow-up) Optimize anonymize_last_borrowers to avoid N+1 queries
78c5195.patch (text/plain), 4.08 KB, created by
Martin Renvoize (ashimema)
on 2026-01-30 11:15:14 UTC
(
hide
)
Description:
Bug 23260: (QA follow-up) Optimize anonymize_last_borrowers to avoid N+1 queries
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2026-01-30 11:15:14 UTC
Size:
4.08 KB
patch
obsolete
>From 78c51955829015359b3a231b247c1b75aa513525 Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@openfifth.co.uk> >Date: Fri, 30 Jan 2026 10:58:00 +0000 >Subject: [PATCH] Bug 23260: (QA follow-up) Optimize anonymize_last_borrowers > to avoid N+1 queries > >This commit refactors Koha::Patrons->anonymize_last_borrowers() to use >a single bulk UPDATE instead of looping through patrons individually. > >Performance Problem: >------------------- >The original implementation had a severe N+1 query problem: >1. Query all patrons with items_last_borrower records >2. Loop through each patron (First N+1) >3. For each patron, search their items_last_borrowers (Second N+1) >4. Update each result set > >For a library with 10,000 patrons and 100,000 items, this would >execute 20,000+ database queries when run as a cronjob. > >The Fix: >-------- >Query the items_last_borrower table directly with all conditions >in a single search, then perform one bulk UPDATE: > > $schema->resultset('ItemsLastBorrower')->search({...})->update({...}) > >This reduces execution from potentially thousands of queries to a >single SQL UPDATE statement, regardless of data volume. > >Why This Pattern: >----------------- >While Koha::Objects->update() exists, it loops through records >individually if the object class has update()/store() methods, >unless {no_triggers => 1} is passed. Working directly with the >resultset guarantees bulk update behavior. > >This pattern is used throughout Koha for bulk operations (see >Koha/Item.pm, Koha/Hold.pm, admin/koha2marclinks.pl, etc). > >Testing: >-------- >prove t/db_dependent/Koha/Patrons.t > >All existing tests pass, confirming identical behavior with >dramatically improved performance. > >Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk> >--- > Koha/Patrons.pm | 40 +++++++++++----------------------------- > 1 file changed, 11 insertions(+), 29 deletions(-) > >diff --git a/Koha/Patrons.pm b/Koha/Patrons.pm >index 64082a61e8a..65131c0463d 100644 >--- a/Koha/Patrons.pm >+++ b/Koha/Patrons.pm >@@ -213,39 +213,21 @@ sub anonymize_last_borrowers { > my $anonymous_patron = C4::Context->preference('AnonymousPatron'); > > my $dtf = Koha::Database->new->schema->storage->datetime_parser; >- my $rs = $self->_resultset->search( >+ >+ # Perform bulk update directly on items_last_borrower table to avoid N+1 queries >+ my $schema = Koha::Database->new->schema; >+ my $items_last_borrower_rs = $schema->resultset('ItemsLastBorrower')->search( > { >- created_on => { '<' => $dtf->format_datetime($older_than_date), }, >- 'items_last_borrowers.borrowernumber' => { 'not' => undef }, # Keep forever >- 'items_last_borrowers.borrowernumber' => { '!=' => $anonymous_patron }, >+ created_on => { '<' => $dtf->format_datetime($older_than_date) }, >+ borrowernumber => { '!=' => $anonymous_patron, 'not' => undef }, >+ 'itemnumber.damaged' => 0, >+ 'itemnumber.itemlost' => 0, >+ 'itemnumber.withdrawn' => 0, > }, >- { >- join => ["items_last_borrowers"], >- distinct => 1, >- } >+ { join => 'itemnumber' } > ); >- my $patrons = Koha::Patrons->_new_from_dbic($rs); >- >- my $nb_rows = 0; >- while ( my $patron = $patrons->next ) { >- my $last_borrowers_to_anonymize = $patron->_result->items_last_borrowers->search( >- { >- ( >- $older_than_date >- ? ( created_on => { '<' => $dtf->format_datetime($older_than_date) } ) >- : (), >- "itemnumber.damaged" => 0, >- "itemnumber.itemlost" => 0, >- "itemnumber.withdrawn" => 0, >- ), >- }, >- { join => ['itemnumber'] } >- ); >- $nb_rows += >- $last_borrowers_to_anonymize->update( { 'items_last_borrower.borrowernumber' => $anonymous_patron } ); >- } > >- return $nb_rows; >+ return $items_last_borrower_rs->update( { borrowernumber => $anonymous_patron } ); > } > > =head3 delete >-- >2.52.0 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 23260
:
92863
|
92864
|
93052
|
94858
|
94859
|
95033
|
95457
|
95458
|
95459
|
95460
|
95651
|
95698
|
95699
|
95700
|
95701
|
95769
|
95770
|
95771
|
95772
|
96791
|
97841
|
97842
|
98044
|
98053
|
98054
|
98064
|
98282
|
98349
|
98352
|
112734
|
112735
|
112736
|
112737
|
112738
|
112739
|
112740
|
112741
|
112742
|
112743
|
117974
|
117975
|
117976
|
117977
|
117978
|
117979
|
117980
|
117981
|
117982
|
117983
|
120522
|
120523
|
120524
|
120525
|
120526
|
120527
|
120528
|
120529
|
120530
|
120531
|
122963
|
122964
|
122965
|
122966
|
122967
|
122968
|
122969
|
122970
|
122971
|
122972
|
145175
|
145176
|
145177
|
145178
|
145179
|
145180
|
145181
|
145182
|
145183
|
145184
|
157106
|
157107
|
157108
|
157109
|
157110
|
157111
|
157112
|
157113
|
157114
|
157115
|
157116
|
159291
|
159292
|
159293
|
159294
|
159298
|
159299
|
159300
|
159301
|
164129
|
164130
|
164131
|
164132
|
164133
|
182000
|
182001
|
182002
|
182003
|
182004
|
182005
|
182006
|
182007
|
182927
|
182930
|
186835
|
186836
|
186837
|
186838
|
186839
|
186840
|
186843
|
186902
|
186942
|
186943
|
186948
|
187768
|
187769
|
187770
|
187771
|
187772
|
187773
|
187774
|
187775
|
187776
|
187777
|
187778
|
192200
|
192201
|
192202
|
192203
|
192204
|
192205
|
192206
|
192207
|
192208
|
192209
|
192210
|
192211
|
192212
|
192213
|
192214
|
192215
|
192216
|
192217
|
192218
|
192219
|
192220
|
192221
|
192222
|
192223
|
192224
|
192225
|
192226
|
192227
|
192228
|
192229
|
192230
|
192231
| 192232 |
192233