To view claims, when clicked, an SQL query is performed to search for claims by vendor and build a select list. If you have so many claims, it takes too long or even creates a timeout problem.
Created attachment 136409 [details] [review] Bug 39001: Improve perf to get claims list by vendor To view claims, when clicked, an SQL query is performed to search for claims by vendor and build a select list. If you have so many claims, it takes too long or even creates a timeout problem. Test plan: 1) Be sure to have vendors and claims on serials 2) Click on "claims", the more claims you have, the longer it will take to appear 3) Apply patch 4) Restart step 2n
Comment on attachment 136409 [details] [review] Bug 39001: Improve perf to get claims list by vendor >From 7dad0a4dbdf0832020ba80e74e7d7536d7f689c5 Mon Sep 17 00:00:00 2001 >From: Thibaud Guillot <thibaud.guillot@biblibre.com> >Date: Tue, 14 Jun 2022 14:54:04 +0200 >Subject: [PATCH] Bug 39001: Improve perf to get claims list by vendor > >To view claims, when clicked, an SQL query is performed to search for >claims by vendor and build a select list. If you have so many claims, it >takes too long or even creates a timeout problem. > >Test plan: > >1) Be sure to have vendors and claims on serials >2) Click on "claims", the more claims you have, the longer it will >take to appear >3) Apply patch >4) Restart step 2 > >https://bugs.koha-community.org/show_bug.cgi?id=31009 >--- > C4/Serials.pm | 3 ++- > serials/claims.pl | 1 - > 2 files changed, 2 insertions(+), 2 deletions(-) > >diff --git a/C4/Serials.pm b/C4/Serials.pm >index 93c8a759d9..c63363f1ad 100644 >--- a/C4/Serials.pm >+++ b/C4/Serials.pm >@@ -134,7 +134,7 @@ sub GetSuppliersWithLateIssues { > my $dbh = C4::Context->dbh; > my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) ); > my $query = qq| >- SELECT DISTINCT id, name >+ SELECT COUNT(*) as count, id, name > FROM subscription > LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid > LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id >@@ -144,6 +144,7 @@ sub GetSuppliersWithLateIssues { > OR serial.STATUS IN ( $statuses ) > ) > AND subscription.closed = 0 >+ GROUP BY aqbooksellers.id > ORDER BY name|; > return $dbh->selectall_arrayref($query, { Slice => {} }); > } >diff --git a/serials/claims.pl b/serials/claims.pl >index 5b5479f749..8c8e85f27c 100755 >--- a/serials/claims.pl >+++ b/serials/claims.pl >@@ -47,7 +47,6 @@ my ($template, $loggedinuser, $cookie) > # supplierlist is returned in name order > my $supplierlist = GetSuppliersWithLateIssues(); > for my $s (@{$supplierlist} ) { >- $s->{count} = scalar GetLateOrMissingIssues($s->{id}); > if ($supplierid && $s->{id} == $supplierid) { > $s->{selected} = 1; > } >-- >2.25.1
I'm thinking a new subroutine for doing the calculation would be in place here rather than making GetSuppliersWithLateIssues more complex, and probably slightly slower for all the other users of this function. It would be good to have these as Koha objects to make creating the queries easier...
(In reply to Joonas Kylmälä from comment #3) > I'm thinking a new subroutine for doing the calculation would be in place > here rather than making GetSuppliersWithLateIssues more complex, and > probably slightly slower for all the other users of this function. It would > be good to have these as Koha objects to make creating the queries easier... Is that serious enough that this patch should be "Failed QA", or is the current patch good as a short term improvemenet, and the new subroutine could be done later as an enhancement?
I create 1O late issues in the Receive page => I click on claims page and I loads the claims of the vendor. I see the 10 claims. It's not slow. Are 10 claims enought ? Is it the right process ?
We have one vendor that has over 3400 late/waiting/claimed issues and it took 1,21 minutes for the data to appear on the page.
Did the following testing: 1) Create a serial attached to a vendor 2) Add late issues to the above serial 3) Ran the following SQL query to add test entries to the serial table INSERT INTO serial (`biblionumber`,`subscriptionid`, `status`) SELECT `biblionumber`, `subscriptionid`, `status` FROM serial; 4) Ended up with 448 serial entries, claims.pl does indeed become very slow. 5) Applied patch 6) Upon visiting /cgi-bin/koha/serials/claims.pl, I get the following error: C4::Serials::GetSuppliersWithLateIssues(): DBI Exception: DBD::mysql::db selectall_arrayref failed: 'koha_kohadev.aqbooksellers.name' isn't in GROUP BY at /kohadevbox/koha/serials/claims.pl line 48 at /usr/share/perl5/DBIx/Class/Exception.pm line 77 My suggestion for this performance issue would be to add pagination to the table.