From a605e9beb482bcdc8eee54c91b73125ebbde2f01 Mon Sep 17 00:00:00 2001
From: Mike Rylander <mrylander@gmail.com>
Date: Tue, 12 Feb 2013 11:51:16 -0500
Subject: [PATCH] Use LIMIT to speed EXISTS subqueries

EXISTS without LIMIT is considered harmful in many cases.  Thus, we will
apply such an explicit planner hint in SuperCat.  This reduces the runtime
of URI lookups on some records from 1.2s+ to less than 1ms.

Signed-off-by: Mike Rylander <mrylander@gmail.com>
Signed-off-by: Ben Shum <bshum@biblio.org>
---
 Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm | 15 +++++++++++----
 1 file changed, 11 insertions(+), 4 deletions(-)

diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm
index 71b4b5f323..dc62eee9d2 100644
--- a/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm
@@ -398,6 +398,7 @@ sub cn_browse {
     if (@$statuses || @$copy_locations) {
         @cp_filter = (
             '-exists' => {
+                limit => 1,
                 from  => 'acp',
 				where => {
                     call_number => { '=' => { '+acn' => 'id' } },
@@ -523,6 +524,7 @@ sub cn_startwith {
     if (@$statuses || @$copy_locations) {
         @cp_filter = (
             '-exists' => {
+                limit => 1,
                 from  => 'acp',
 				where => {
                     call_number => { '=' => { '+acn' => 'id' } },
@@ -2159,7 +2161,8 @@ sub new_record_holdings {
                 call_number => { '=' => {'+acn'=>'id'} },
                 deleted => 'f',
                 circ_lib => \@ou_ids
-              }
+              },
+              limit => 1
             }
         }
     ]);
@@ -2182,6 +2185,7 @@ sub new_record_holdings {
             %subselect = (
                 owning_lib => \@uri_ou_ids,
                 '-exists'  => {
+                    limit => 1,
                     from  => { auricnm => 'auri' },
                     where => {
                         call_number => { '=' => {'+acn'=>'id'} },
@@ -2199,7 +2203,8 @@ sub new_record_holdings {
                         call_number => { '=' => {'+acn'=>'id'} },
                         deleted => 'f',
                         circ_lib => \@ou_ids
-                      }
+                      },
+                      limit => 1
                     }
                 },
                 { '-and' => [
@@ -2209,7 +2214,8 @@ sub new_record_holdings {
                         where => {
                             call_number => { '=' => {'+acn'=>'id'} },
                             '+auri' => { active => 't' }
-                        }
+                        },
+                        limit => 1
                     }}
                 ]}
             ]);
@@ -2281,7 +2287,8 @@ sub new_record_holdings {
         { owning_lib => \@ou_ids },
         { '-exists'  =>
             { from  => 'sdist',
-              where => { holding_lib => \@ou_ids }
+              where => { holding_lib => \@ou_ids },
+              limit => 1
             }
         }
     ]);
-- 
2.11.0