[Koha-bugs] [Bug 31009] Claims list take too much times (timeout when you have so many claims) on serials

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31009

--- Comment #2 from Thibaud Guillot <thibaud.guillot at 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 at 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.