[Bug 31009] New: Claims list take too much times (timeout when you have so many claims) on serials
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009 Bug ID: 31009 Summary: Claims list take too much times (timeout when you have so many claims) on serials Change sponsored?: --- Product: Koha Version: 19.11 Hardware: All OS: All Status: NEW Severity: minor Priority: P5 - low Component: Serials Assignee: koha-bugs@lists.koha-community.org Reporter: thibaud.guillot@biblibre.com QA Contact: testopia@bugs.koha-community.org 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. -- 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=31009 Thibaud Guillot <thibaud.guillot@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version|19.11 |master -- 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=31009 Thibaud Guillot <thibaud.guillot@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- 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=31009 --- Comment #1 from Thibaud Guillot <thibaud.guillot@biblibre.com> --- Created attachment 136409 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=136409&action=edit 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 -- 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=31009 --- Comment #2 from Thibaud Guillot <thibaud.guillot@biblibre.com> --- Comment on attachment 136409 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=136409 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
-- 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=31009 Joonas Kylmälä <joonas.kylmala@iki.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |joonas.kylmala@iki.fi --- Comment #3 from Joonas Kylmälä <joonas.kylmala@iki.fi> --- 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... -- 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=31009 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |thibaud.guillot@biblibre.co |ity.org |m -- 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=31009 Magnus Enger <magnus@libriotech.no> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |magnus@libriotech.no --- Comment #4 from Magnus Enger <magnus@libriotech.no> --- (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? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009 Dept Num <cecile.lambour@univ-brest.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |cecile.lambour@univ-brest.f | |r --- Comment #5 from Dept Num <cecile.lambour@univ-brest.fr> --- 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 ? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Claims list take too much |Claims list on serials is |times (timeout when you |too slow with many existing |have so many claims) on |late issues |serials | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009 Anneli Österman <anneli.osterman@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |anneli.osterman@koha-suomi. | |fi --- Comment #6 from Anneli Österman <anneli.osterman@koha-suomi.fi> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009 Pedro Amorim <pedro.amorim@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |pedro.amorim@ptfs-europe.co | |m Status|Needs Signoff |Failed QA --- Comment #7 from Pedro Amorim <pedro.amorim@ptfs-europe.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009 Emmi Takkinen <emmi.takkinen@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |emmi.takkinen@koha-suomi.fi --- Comment #8 from Emmi Takkinen <emmi.takkinen@koha-suomi.fi> --- I agree with Pedro, table should be paginated. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009 Michaela Sieber <michaela.sieber@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de, | |michaela.sieber@kit.edu -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009 --- Comment #9 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- This is still an issue in current systems. I believe this is the old "mysql strict" (bug Bug 17258 ): 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 Should we still go with this patch and try to improve the SQL statement? A rewrite for API use and pagination seems much more complex and might better wait for the new acq module. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org