From a6365e9fdaf7ee4ab01a5c18971244076c80c5c7 Mon Sep 17 00:00:00 2001
From: Mike Rylander <mrylander@gmail.com>
Date: Sat, 16 Jul 2011 12:53:13 -0400
Subject: [PATCH] Bad join condition in secondary (physical) address lookup,
causing terrible performance of circ aging
Signed-off-by: Mike Rylander <mrylander@gmail.com>
---
Open-ILS/src/sql/Pg/090.schema.action.sql | 2 +-
.../sql/Pg/upgrade/XXX.schema.aging_circ_view.sql | 23 ++++++++++++++++++++++
2 files changed, 24 insertions(+), 1 deletion(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXX.schema.aging_circ_view.sql
diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql
index f15107d891..44a3236385 100644
--- a/Open-ILS/src/sql/Pg/090.schema.action.sql
+++ b/Open-ILS/src/sql/Pg/090.schema.action.sql
@@ -212,7 +212,7 @@ CREATE OR REPLACE VIEW action.all_circulation AS
JOIN asset.call_number cn ON (cp.call_number = cn.id)
JOIN actor.usr p ON (circ.usr = p.id)
LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
- LEFT JOIN actor.usr_address b ON (p.billing_address = a.id);
+ LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
DECLARE
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXX.schema.aging_circ_view.sql b/Open-ILS/src/sql/Pg/upgrade/XXX.schema.aging_circ_view.sql
new file mode 100644
index 0000000000..8e3c440287
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXX.schema.aging_circ_view.sql
@@ -0,0 +1,23 @@
+CREATE OR REPLACE VIEW action.all_circulation AS
+ SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
+ copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
+ circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
+ stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
+ max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
+ max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
+ FROM action.aged_circulation
+ UNION ALL
+ SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
+ cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
+ cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
+ circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
+ circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
+ circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
+ circ.parent_circ
+ FROM action.circulation circ
+ JOIN asset.copy cp ON (circ.target_copy = cp.id)
+ JOIN asset.call_number cn ON (cp.call_number = cn.id)
+ JOIN actor.usr p ON (circ.usr = p.id)
+ LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
+ LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
+
--
2.11.0