Bug 25223

Summary: Ordered.pl can have poor performance on large databases
Product: Koha Reporter: Nick Clemens <nick>
Component: AcquisitionsAssignee: Nick Clemens <nick>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: 1joynelson, jonathan.druart, katrin.fischer, lucas, martin.renvoize, tomascohen
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
20.05.00, 19.11.06, 19.05.11
Attachments: Bug 25223: Make join of aqbasket and aqorders explicit
Bug 25223: Make join of aqbasket and aqorders explicit
Bug 25223: Make join of aqbasket and aqorders explicit

Description Nick Clemens 2020-04-20 18:41:02 UTC
Ordered.pl has an implied join, this seems to sometimes cause issues on large databases
Comment 1 Nick Clemens 2020-04-20 18:44:10 UTC
Created attachment 103312 [details] [review]
Bug 25223: Make join of aqbasket and aqorders explicit

This is simply a SQL change that makes things a bit easier to read
and also prevents too much data being returned in a large system

To test:
1 - Have some orders in your system in the ordered status
2 - Click the 'Ordered' amount for your budget from Acquisitions home
3 - Note the view of orders, possibly screen shot
4 - Apply patch
5 - Restart all the things
6 - Reload the ordered page
7 - Confirm nothing has changed
Comment 2 David Nind 2020-04-21 01:23:29 UTC
Created attachment 103320 [details] [review]
Bug 25223: Make join of aqbasket and aqorders explicit

This is simply a SQL change that makes things a bit easier to read
and also prevents too much data being returned in a large system

To test:
1 - Have some orders in your system in the ordered status
2 - Click the 'Ordered' amount for your budget from Acquisitions home
3 - Note the view of orders, possibly screen shot
4 - Apply patch
5 - Restart all the things
6 - Reload the ordered page
7 - Confirm nothing has changed

Signed-off-by: David Nind <david@davidnind.com>
Comment 3 Jonathan Druart 2020-04-21 08:27:48 UTC
Nick, do you have numbers?
Comment 4 Nick Clemens 2020-04-21 10:19:24 UTC
(In reply to Jonathan Druart from comment #3)
> Nick, do you have numbers?

There were 6000 baskets and 18000 orders in the system. I can say only some funds showed an issue.
Comment 5 Jonathan Druart 2020-04-21 11:43:01 UTC
Created attachment 103364 [details] [review]
Bug 25223: Make join of aqbasket and aqorders explicit

This is simply a SQL change that makes things a bit easier to read
and also prevents too much data being returned in a large system

To test:
1 - Have some orders in your system in the ordered status
2 - Click the 'Ordered' amount for your budget from Acquisitions home
3 - Note the view of orders, possibly screen shot
4 - Apply patch
5 - Restart all the things
6 - Reload the ordered page
7 - Confirm nothing has changed

Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 6 Martin Renvoize 2020-04-22 08:32:00 UTC
Nice work everyone!

Pushed to master for 20.05
Comment 7 Joy Nelson 2020-05-08 18:47:27 UTC
Backported to 19.11.x for 19.11.06
Comment 8 Lucas Gass 2020-05-14 18:09:46 UTC
backported to 19.05.x for 19.05.11