From 6dea8e1dd52aa2c88f72614c8f9cdc7cc45107ef Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Sat, 31 Dec 2005 17:17:18 +0000
Subject: [PATCH] sped up page_down by about 20%

git-svn-id: svn://svn.open-ils.org/ILS/trunk@2567 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 .../Application/Storage/Publisher/asset.pm    | 118 ++++++++----------
 Open-ILS/src/sql/Pg/040.schema.asset.sql      |   1 +
 2 files changed, 52 insertions(+), 67 deletions(-)

diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm
index b14281f385..a0995d88a8 100644
--- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm
@@ -31,23 +31,19 @@ sub cn_browse_pagedown {
 	}
 
 	my $sql = <<"	SQL";
-		select * from (
-			select
-			        cn.label,
-			        cn.owning_lib,
-		        	cn.record,
-			        cn.id
-			from
-			        $table cn
-			        join $descendants d
-		        	        on (d.id = cn.owning_lib)
-			where
-			        upper(label) > ?
-			        or ( cn.id > ? and upper(label) = ? )
-			order by upper(label)
-			limit 1000
-		) as foo
-		order by 1,4
+		select
+		        cn.label,
+		        cn.owning_lib,
+	        	cn.record,
+		        cn.id
+		from
+		        $table cn
+		        join $descendants d
+	        	        on (d.id = cn.owning_lib)
+		where
+		        upper(label) > ?
+		        or ( cn.id > ? and upper(label) = ? )
+		order by upper(label), 4, 2
 		limit $size;
 	SQL
 
@@ -89,26 +85,22 @@ sub cn_browse_pageup {
 
 	my $sql = <<"	SQL";
 		select * from (
-			select * from (
-				select
-				        cn.label,
-				        cn.owning_lib,
-			        	cn.record,
-				        cn.id
-				from
-				        $table cn
-				        join $descendants d
-			        	        on (d.id = cn.owning_lib)
-				where
-				        upper(label) < ?
-				        or ( cn.id < ? and upper(label) = ? )
-				order by upper(label) desc
-				limit 1000
-			) as foo
-			order by 1 desc, 4 desc
+			select
+			        cn.label,
+			        cn.owning_lib,
+		        	cn.record,
+			        cn.id
+			from
+			        $table cn
+			        join $descendants d
+		        	        on (d.id = cn.owning_lib)
+			where
+			        upper(label) < ?
+			        or ( cn.id < ? and upper(label) = ? )
+			order by upper(label) desc, 4 desc, 2 desc
 			limit $size
 		) as bar
-		order by 1,4;
+		order by 1,4,2;
 	SQL
 
 	my $sth = asset::call_number->db_Main->prepare($sql);
@@ -149,45 +141,37 @@ sub cn_browse_target {
 	}
 
 	my $top_sql = <<"	SQL";
-		select * from (
-			select * from (
-				select
-				        cn.label,
-				        cn.owning_lib,
-			        	cn.record,
-				        cn.id
-				from
-				        $table cn
-				        join $descendants d
-			        	        on (d.id = cn.owning_lib)
-				where
-				        upper(label) < ?
-				order by upper(label) desc
-				limit 1000
-			) as foo
-			order by 1 desc, 4 desc
-			limit $topsize
-		) as bar
-		order by 1,4;
-	SQL
-
-	my $bottom_sql = <<"	SQL";
 		select * from (
 			select
-	        		cn.label,
+			        cn.label,
 			        cn.owning_lib,
-			        cn.record,
-		        	cn.id
+			       	cn.record,
+			        cn.id
 			from
 			        $table cn
 			        join $descendants d
-		        	        on (d.id = cn.owning_lib)
+			       	        on (d.id = cn.owning_lib)
 			where
-			        upper(label) >= ?
-			order by upper(label)
-			limit 1000
-		) as foo
-		order by 1,4
+			        upper(label) < ?
+			order by upper(label) desc, 4 desc, 2 desc
+			limit $topsize
+		) as bar
+		order by 1,4,2;
+	SQL
+
+	my $bottom_sql = <<"	SQL";
+		select
+        		cn.label,
+		        cn.owning_lib,
+		        cn.record,
+	        	cn.id
+		from
+		        $table cn
+		        join $descendants d
+	        	        on (d.id = cn.owning_lib)
+		where
+		        upper(label) >= ?
+		order by upper(label),4,2
 		limit $bottomsize;
 	SQL
 
diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql
index ab372b52ea..b8ce056e13 100644
--- a/Open-ILS/src/sql/Pg/040.schema.asset.sql
+++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql
@@ -122,6 +122,7 @@ CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
+CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
 
 CREATE TABLE asset.call_number_note (
 	id		BIGSERIAL			PRIMARY KEY,
-- 
2.43.2