From 007d6166691e83602c6a204986d6a1ee7c8e9392 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>
---
 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