sped up page_down by about 20%
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Sat, 31 Dec 2005 17:17:18 +0000 (17:17 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Sat, 31 Dec 2005 17:17:18 +0000 (17:17 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@2567 dcc99617-32d9-48b4-a31d-7c20da2025e4

Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm
Open-ILS/src/sql/Pg/040.schema.asset.sql

index b14281f..a0995d8 100644 (file)
@@ -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);
@@ -150,44 +142,36 @@ 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
 
index ab372b5..b8ce056 100644 (file)
@@ -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,