Bug 5866

Summary: At larger hold volume, waitingreserves.pl for all libraries times out, can cause generalized slowness
Product: Koha Reporter: Liz Rea <wizzyrea>
Component: Hold requestsAssignee: Chris Cormack <chris>
Status: CLOSED FIXED QA Contact: Bugs List <koha-bugs>
Severity: minor    
Priority: PATCH-Sent (DO NOT USE) CC: chris, gmcharlt, koha.sekjal, nengard, wizzyrea
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:
Attachments: Bug 5866 : speeding up holds awaiting pickup report
Bug 5866 : Updated patch to speed up holds awaiting pickup
Bug 5866 : Updated patch to speed up holds awaiting pickup

Description Liz Rea 2011-03-15 15:05:22 UTC
In a consortium with 35 libraries, with hundreds of holds waiting pickup:

On the "Holds awaiting pickup for your Library" report, selecting "View all branches" the report will not complete (we waited several
minutes - the computer went to sleep before the report finished).

While that report was trying to run, patron searches took 5-8 seconds instead
of 2-5.  Catalog searches took 10 seconds.  Cataloging searches
(/cataloguing/addbooks.pl?q=twilight) took 7-10 seconds.
Comment 1 Chris Cormack 2011-08-25 01:25:08 UTC
Looking into this now
Comment 2 Chris Cormack 2011-08-25 01:51:00 UTC
The problem here, is the query is this

SELECT borrowernumber,reservedate,itemnumber,waitingdate FROM reserves         WHERE   priority='0'  AND found='W';

With makes use of no indexes

 CREATE INDEX priorityfoundidx ON reserves (priority,found);

Will create a composity key, which then will be used by the query which should reduce the time taken to search a lot.

Will create a patch
Comment 3 Chris Cormack 2011-08-25 02:08:49 UTC Comment hidden (obsolete)
Comment 4 Chris Cormack 2011-08-25 02:09:51 UTC
Please test with a big db of holds.
Comment 5 Chris Cormack 2011-08-25 03:21:59 UTC Comment hidden (obsolete)
Comment 6 Liz Rea 2011-08-25 19:38:29 UTC
Created attachment 5151 [details] [review]
Bug 5866 : Updated patch to speed up holds awaiting pickup

This reduced execution time on the report from timeout (1400 holds waiting + 200 holds over) to 6 or so seconds, with 1566 holds over (test data, no holds waiting).

Signed-off-by: Liz Rea <lrea@nekls.org>
Comment 7 Ian Walls 2011-08-25 19:54:16 UTC
Just adds an index, and uses a subroutine that retrieves less unnecessary data (GetMember v. GetMemberDetails, which fetchs account information as well as flags).  Marking as Passed QA.
Comment 8 Chris Cormack 2011-08-26 03:32:58 UTC
Pushed, please test
Comment 9 Ian Walls 2011-10-16 11:44:15 UTC
Marking resolved.
Comment 10 Chris Nighswonger 2011-10-26 18:02:11 UTC
The fix for this bug was published in the 3.4.5 release. If you were the reporter of this bug, please take time to verify the fix and update the status of this bug report accordingly. If the bug is fixed to your satisfaction, please close this report.