Reporter source Hold/Item Ratio per Bib and Pickup Library (and Descendants) is not effecient

[Steve Callender]

The reporting source, Hold/Item Ratio per Bib and Pickup Library (and Descendants), class id="rhcrpbapd", when used on a fairly large system can cause the DB to churn in upwards of 3-4 hours to complete.

Mike Rylander has been looking on ways to optimize this query in order to achieve better performance and will be submitting a branch to take a look at. Initial tests have brought the speed down from about 3 hours and 30 minutes to 5 minutes.

[Mike Rylander (mrylander)]

As promised by Steve, a branch is available at: https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/miker/lp-2069062-faster-hold-ratio-source

From the commit message:

The Hold Ratio reporting source finds the common ancestor of the hold pickup library and each relevant copy's circ library using two calls to the ancestor.org_unit_ancestor_at_depth() function. This is the most direct way to determine if two org units share a specific common ancestor, but it is also very expensive.

This commit restructures the query by precalculating the set of descendants for each org unit, which allows for a faster test of whether the pickup and circ libs are both in the same descendant list. This is definitionally the same test as having a particular common ancestor, but can be performed MUCH faster.

[Elizabeth Davis (elidavis)]

We've had this running on our production server since early June, and it's working as expected. It's resolved a lot of report backups and time-outs for us.

I have tested this code and consent to signing off on it with my name, Elizabeth Davis and my email address, <email address hidden>.