From 5fb92d384aeca7ed0e8b1c34d97447a35f00f256 Mon Sep 17 00:00:00 2001
From: Mike Rylander <mrylander@gmail.com>
Date: Fri, 8 Feb 2013 10:15:39 -0500
Subject: [PATCH] Speed up sorted-related-holds query

In several interfaces, we use a server side method which gathers statistics
about a hold: related holds, it's position in the (approximate) queue, the
estimated wait time, etc.  Within this method is a relatively complicated
json_query that returns the list of related, (FIFO-ish) sorted holds -- ones
that could be filled by a copy which could fill the hold in question.  This
commit restructures that query so as to make it faster when the list of
related holds is large, by removing duplicate (cartesian product, actually)
hold ids that were being fed into an INNER JOIN clause.

Testing shows a speed increase of 4x for related-hold queue of around 675
holds [~2s -> ~0.5s] on a relatively large Evergreen installation,
appropriately tuned.  The speed improvement gets larger with longer queues.
There is no observed decrease in speed for smaller queue sizes.

Signed-off-by: Mike Rylander <mrylander@gmail.com>
Signed-off-by: Ben Shum <bshum@biblio.org>
---
 .../lib/OpenILS/Application/Circ/Holds.pm     | 21 ++++++++++---------
 1 file changed, 11 insertions(+), 10 deletions(-)

diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm
index 03f6c982af..019b0333e3 100644
--- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm
@@ -1332,18 +1332,22 @@ sub retrieve_hold_queue_status_impl {
         # fetch cut_in_line and request_time since they're in the order_by
         # and we're asking for distinct values
         select => {ahr => ['id', 'cut_in_line', 'request_time']},
-        from   => {
-            ahr => {
-                'ahcm' => {
-                    join => {
+        from   => 'ahr',
+        where => {
+            id => { in => {
+                select => { ahcm => ['hold'] },
+                from   => {
+                    'ahcm' => {
                         'ahcm2' => {
                             'class' => 'ahcm',
                             'field' => 'target_copy',
                             'fkey'  => 'target_copy'
                         }
                     }
-                }
-            }
+                },
+                where => { '+ahcm2' => { hold => $hold->id } },
+                distinct => 1
+            }}
         },
         order_by => [
             {
@@ -1355,10 +1359,7 @@ sub retrieve_hold_queue_status_impl {
             },
             { "class" => "ahr", "field" => "request_time" }
         ],
-        distinct => 1,
-        where => {
-            '+ahcm2' => { hold => $hold->id }
-        }
+        distinct => 1
     });
 
     if (!@$q_holds) { # none? maybe we don't have a map ...
-- 
2.43.2