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.
Looking into this now
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
Created attachment 5138 [details] [review] Bug 5866 : speeding up holds awaiting pickup report
Please test with a big db of holds.
Created attachment 5143 [details] [review] Bug 5866 : Updated patch to speed up holds awaiting pickup
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>
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.
Pushed, please test
Marking resolved.
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.