Bug 5866 - At larger hold volume, waitingreserves.pl for all libraries times out, can cause generalized slowness
Summary: At larger hold volume, waitingreserves.pl for all libraries times out, can ca...
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Hold requests (show other bugs)
Version: Main
Hardware: All All
: PATCH-Sent (DO NOT USE) minor (vote)
Assignee: Chris Cormack
QA Contact: Bugs List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-03-15 15:05 UTC by Liz Rea
Modified: 2012-10-25 23:10 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 5866 : speeding up holds awaiting pickup report (1.60 KB, patch)
2011-08-25 02:08 UTC, Chris Cormack
Details | Diff | Splinter Review
Bug 5866 : Updated patch to speed up holds awaiting pickup (2.62 KB, patch)
2011-08-25 03:21 UTC, Chris Cormack
Details | Diff | Splinter Review
Bug 5866 : Updated patch to speed up holds awaiting pickup (2.86 KB, patch)
2011-08-25 19:38 UTC, Liz Rea
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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.