From af966c3f322b4ad9d5a046026f57da5ca5cc995d Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 14 Feb 2014 12:01:37 -0500 Subject: [PATCH] LP1272316 - Calculate proximity in the DB for speed We need to calculate and store a (potenially adjusted) proxmity for each hold and copy for use in targetting and optimization of op capture. Before this commit, we do that within the hold target code itself. Now we'll do it when the hold-copy map is inserted, because we have the same information available at that time as we have in the targeter. This will both speed up the apparent cost of the calculation, because it avoids the cost of a network round-trip, and the total number of SELECTs we must issue, because the proximity value will now be cached for use by the proximity map function. Backward compatability is retained for the create_prox_map() function in case any other code is depending on that. Signed-off-by: Mike Rylander Signed-off-by: Chris Sharp Signed-off-by: Ben Shum --- .../Application/Storage/Publisher/action.pm | 24 +++++++++++-------- Open-ILS/src/sql/Pg/090.schema.action.sql | 9 +++++++ ...X.schema.pre-calculate-prox-adjustment.sql | 16 +++++++++++++ 3 files changed, 39 insertions(+), 10 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.pre-calculate-prox-adjustment.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm index 449d26279e..713c10782f 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm @@ -1579,15 +1579,17 @@ sub new_hold_copy_targeter { $found_copy = 1 if($find_copy and grep $_ == $find_copy, @$all_copies); # map the potentials, so that we can pick up checkins - # XXX Loop-based targeting may require that /only/ copies from this loop should be added to - # XXX the potentials list. If this is the cased, hold_copy_map creation will move down further. + my $hold_copy_map = {}; + $hold_copy_map->{$_->hold}->{$_->target_copy} = $_->proximity + for ( + map { + action::hold_copy_map->create( { hold => $hold->id, target_copy => $_->id } ) + } @$all_copies + ); + my $pu_lib = ''.$hold->pickup_lib; - my $prox_list = create_prox_list( $self, $pu_lib, $all_copies, $hold ); + my $prox_list = create_prox_list( $self, $pu_lib, $all_copies, $hold, $hold_copy_map ); $log->debug( "\tMapping ".scalar(@$all_copies)." potential copies for hold ".$hold->id); - for my $prox ( keys %$prox_list ) { - action::hold_copy_map->create( { proximity => $prox, hold => $hold->id, target_copy => $_ } ) - for keys( %{{ map { $_->id => 1 } @{$$prox_list{$prox}} }} ); - } #$client->status( new OpenSRF::DomainObject::oilsContinueStatus ); @@ -1671,7 +1673,7 @@ sub new_hold_copy_targeter { $prox_list = create_prox_list( $self, $pu_lib, [ grep { $_->status == 0 || $_->status == 7 } @good_copies ], - $hold + $hold, $hold_copy_map ); $all_copies = [ grep { ''.$_->circ_lib ne $pu_lib && ( $_->status == 0 || $_->status == 7 ) } @good_copies ]; @@ -1772,7 +1774,7 @@ sub new_hold_copy_targeter { die "OK\n"; } - $prox_list = create_prox_list( $self, $pu_lib, $all_copies, $hold ); + $prox_list = create_prox_list( $self, $pu_lib, $all_copies, $hold, $hold_copy_map ); $client->status( new OpenSRF::DomainObject::oilsContinueStatus ); @@ -2266,11 +2268,13 @@ sub create_prox_list { my $lib = shift; my $copies = shift; my $hold = shift; + my $hold_copy_map = shift || {}; my %prox_list; my $editor = new_editor; for my $cp (@$copies) { - my ($prox) = $self->method_lookup('open-ils.storage.asset.copy.proximity')->run( $cp, $lib, $hold ); + my $prox = $hold_copy_map->{"$hold"}->{"$cp"}; # Allow CDBI stringification to get the pkey + ($prox) = $self->method_lookup('open-ils.storage.asset.copy.proximity')->run( $cp, $lib, $hold ) unless (defined $prox); next unless (defined($prox)); my $copy_circ_lib = ''.$cp->circ_lib; diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 35ab53bc2e..ad78f50e86 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -1344,4 +1344,13 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$ +BEGIN + NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy); + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update (); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.pre-calculate-prox-adjustment.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.pre-calculate-prox-adjustment.sql new file mode 100644 index 0000000000..723699d065 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.pre-calculate-prox-adjustment.sql @@ -0,0 +1,16 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$ +BEGIN + NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy); + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update (); + +-- Now, cause the update we need in a HOT-friendly manner (http://pgsql.tapoueh.org/site/html/misc/hot.html) +UPDATE action.hold_copy_map SET proximity = proximity WHERE proximity IS NULL; + +COMMIT; + -- 2.43.2