Bug 31009 - Claims list on serials is too slow with many existing late issues
Summary: Claims list on serials is too slow with many existing late issues
Status: Failed QA
Alias: None
Product: Koha
Classification: Unclassified
Component: Serials (show other bugs)
Version: Main
Hardware: All All
: P5 - low minor (vote)
Assignee: Thibaud Guillot
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-06-22 09:32 UTC by Thibaud Guillot
Modified: 2023-12-22 10:45 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 39001: Improve perf to get claims list by vendor (2.02 KB, patch)
2022-06-22 10:15 UTC, Thibaud Guillot
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Thibaud Guillot 2022-06-22 09:32:50 UTC
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.
Comment 1 Thibaud Guillot 2022-06-22 10:15:24 UTC
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 2 Thibaud Guillot 2022-06-22 10:16:44 UTC Comment hidden (obsolete)
Comment 3 Joonas Kylmälä 2022-09-03 08:58:36 UTC
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...
Comment 4 Magnus Enger 2023-03-27 10:03:31 UTC
(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?
Comment 5 Dept Num 2023-06-30 13:07:36 UTC
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 ?
Comment 6 Anneli Österman 2023-08-18 10:52:48 UTC
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.
Comment 7 Pedro Amorim 2023-12-22 10:45:41 UTC
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.