Bug 36447

Summary: Circ rules slow to load when many itemtypes and categories
Product: Koha Reporter: Nick Clemens (kidclamp) <nick>
Component: Staff interfaceAssignee: Kyle M Hall (khall) <kyle>
Status: RESOLVED FIXED QA Contact: Marcel de Rooy <m.de.rooy>
Severity: major    
Priority: P5 - low CC: evelyn, fridolin.somers, gmcharlt, jonathan.druart, kyle.m.hall, kyle, lucas, m.de.rooy, tomascohen
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
24.05.00,23.11.05
Circulation function:
Bug Depends on:    
Bug Blocks: 36673    
Attachments: Bug 36447: Circ rules slow to load when many itemtypes and categories
Bug 36447: Circ rules slow to load when many itemtypes and categories
Bug 36447: Circ rules slow to load when many itemtypes and categories

Description Nick Clemens (kidclamp) 2024-03-27 20:41:13 UTC
It seems that we loop all categories and item types to build the circ matrix:
 169                         <tbody>
 170                             [% SET row_count = 0 %]
 171                             [% FOREACH c IN categorycodes %]
 172                                 [% SET c = '' UNLESS c.defined %]
 173                                 [% FOREACH i IN itemtypes %]


This means that when we have a large number of both, the page becomes increasingly slow:
Try loading the page several times, notings the load time
Then:
perl -e 'use t::lib::TestBuilder; my $builder=t::lib::TestBuilder->new(); for( my $i=0;$i<50;$i++){ $builder->build({ source=>"Itemtype"}); }'
Note load times, then:
perl -e 'use t::lib::TestBuilder; my $builder=t::lib::TestBuilder->new(); for( my $i=0;$i<50;$i++){ $builder->build({ source=>"Category"}); }'
Note load times, then run each again
NOte load times, I saw around 15 seconds when I had ~100 of each
Comment 1 Kyle M Hall (khall) 2024-03-28 15:05:21 UTC
Created attachment 164065 [details] [review]
Bug 36447: Circ rules slow to load when many itemtypes and categories

It seems that we loop all categories and item types to build the circ
matrix. We should only loop over values that have actually been used
in circulation rules.

Test Plan:
1) Create 1000 itemtypes and category codes. You can use the following script:
use t::lib::TestBuilder;
my $builder = t::lib::TestBuilder->new();
$builder->build( { source => 'Category' } ) for 0..1000;
$builder->build( { source => 'Itemtype' } ) for 0..1000;
2) Note the lengthy load time for smart-rules.pl
3) Apply this patch
4) Restart all the things!
5) Reload the page
6) Note the much faster load time!
Comment 2 Owen Leonard 2024-03-28 16:37:33 UTC
Created attachment 164083 [details] [review]
Bug 36447: Circ rules slow to load when many itemtypes and categories

It seems that we loop all categories and item types to build the circ
matrix. We should only loop over values that have actually been used
in circulation rules.

Test Plan:
1) Create 1000 itemtypes and category codes. You can use the following
   script:

   use t::lib::TestBuilder;
   my $builder = t::lib::TestBuilder->new();
   $builder->build( { source => 'Category' } ) for 0..1000;
   $builder->build( { source => 'Itemtype' } ) for 0..1000;

2) Note the lengthy load time for smart-rules.pl
3) Apply this patch
4) Restart all the things!
5) Reload the page
6) Note the much faster load time!

Signed-off-by: Owen Leonard <oleonard@myacpl.org>
Comment 3 Marcel de Rooy 2024-03-29 09:46:00 UTC
Created attachment 164121 [details] [review]
Bug 36447: Circ rules slow to load when many itemtypes and categories

It seems that we loop all categories and item types to build the circ
matrix. We should only loop over values that have actually been used
in circulation rules.

Test Plan:
1) Create 1000 itemtypes and category codes. You can use the following
   script:

   use t::lib::TestBuilder;
   my $builder = t::lib::TestBuilder->new();
   $builder->build( { source => 'Category' } ) for 0..1000;
   $builder->build( { source => 'Itemtype' } ) for 0..1000;

2) Note the lengthy load time for smart-rules.pl
3) Apply this patch
4) Restart all the things!
5) Reload the page
6) Note the much faster load time!

Signed-off-by: Owen Leonard <oleonard@myacpl.org>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 4 Katrin Fischer 2024-04-02 16:01:04 UTC
Pushed for 24.05!

Well done everyone, thank you!
Comment 5 Evelyn Hartline 2024-04-10 20:26:12 UTC
Would it be possible to backport this for 23.11?
Comment 6 Fridolin Somers 2024-04-17 15:23:54 UTC
Pushed to 23.11.x for 23.11.05
Comment 7 Lucas Gass (lukeg) 2024-05-13 19:43:28 UTC
Missing 23.05.x dependencies, no backport.