From 69f83682e0f2c0e716485c7c2ade03b105d90200 Mon Sep 17 00:00:00 2001 From: Liam Whalen Date: Sun, 27 Oct 2013 23:45:19 -0700 Subject: [PATCH] Function-based bibs-by-item-age enhancement I added a slight tweak. When looping over the libs to find if they have any newer records, if the result set already has enough records in it to statisfy the offset and limit, then the SQL only looks for records that are newer than the oldest record in the result set currenlty being built.. Signed-off-by: Liam Whalen --- .../perlmods/lib/OpenILS/Application/SuperCat.pm | 2 +- Open-ILS/src/sql/Pg/999.functions.global.sql | 38 +++++++++++++++++---- .../Pg/upgrade/XXXX.function.bibs_by_item_age.sql | 39 ++++++++++++++++++---- 3 files changed, 65 insertions(+), 14 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm index 322919c320..0a63048750 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm @@ -648,7 +648,7 @@ sub new_books_by_item { "open-ils.cstore.json_query.atomic", { from => [ - biblio.records_by_item_age, + 'biblio.records_by_item_age', $page_size, $offset, '{' . join(',', @ou_ids) . '}', diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index 39eda9bee6..1574a05471 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -26,6 +26,9 @@ DECLARE v_temprec record; v_found INT4; v_cursor REFCURSOR; + v_oldest timestamptz := NUll; + v_c_oldest timestamptz := NULL; + v_key_count INT8; BEGIN IF array_length(p_circ_libs,1) > 0 THEN @@ -37,12 +40,26 @@ BEGIN FOREACH v_circ_lib IN ARRAY a_circ_libs LOOP v_found := 0; v_seen := ''; - - open v_cursor NO SCROLL FOR - SELECT c.call_number, c.create_date, c.status, c.location - FROM asset.copy c - WHERE c.circ_lib = v_circ_lib AND NOT c.deleted - ORDER BY c.create_date DESC; + v_c_oldest := NULL; + + -- If the number of keys in v_results is greater than or equal to + -- the number of results we need for the current page, then only look + -- for records that are newer than items currently in v_results + SELECT COUNT(keys) FROM skeys(v_results) AS keys INTO v_key_count; + IF v_key_count >= p_limit + p_offset THEN + open v_cursor NO SCROLL FOR + SELECT c.call_number, c.create_date, c.status, c.location + FROM asset.copy c + WHERE c.circ_lib = v_circ_lib AND NOT c.deleted + AND c.create_date > v_oldest + ORDER BY c.create_date DESC; + ELSE + open v_cursor NO SCROLL FOR + SELECT c.call_number, c.create_date, c.status, c.location + FROM asset.copy c + WHERE c.circ_lib = v_circ_lib AND NOT c.deleted + ORDER BY c.create_date DESC; + END IF; LOOP -- This loop gets up to a page worth of copies from each circ lib, if that many exist for each. FETCH v_cursor INTO v_temprec; @@ -74,11 +91,20 @@ BEGIN v_found := v_found + 1; v_results := v_results || hstore( v_record::TEXT, v_temprec.create_date::TEXT ); + IF v_c_oldest IS NULL OR v_c_oldest > v_temprec.create_date THEN + v_c_oldest := v_temprec.create_date; + END IF; + EXIT WHEN v_found = p_limit + p_offset; END LOOP; CLOSE v_cursor; + -- Update oldest information based on oldest row added in current loop + IF v_oldest IS NULL OR v_oldest < v_c_oldest THEN + v_oldest := v_c_oldest; + END IF; + END LOOP; RETURN QUERY SELECT KEY::INT8, value::timestamptz FROM each(v_results) ORDER BY value::timestamptz DESC, KEY::INT8 LIMIT p_limit OFFSET p_offset; RETURN; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.bibs_by_item_age.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.bibs_by_item_age.sql index 3dc321c78f..131a055ea1 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.bibs_by_item_age.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.bibs_by_item_age.sql @@ -14,6 +14,9 @@ DECLARE v_temprec record; v_found INT4; v_cursor REFCURSOR; + v_oldest timestamptz := NUll; + v_c_oldest timestamptz := NULL; + v_key_count INT8; BEGIN IF array_length(p_circ_libs,1) > 0 THEN @@ -25,12 +28,26 @@ BEGIN FOREACH v_circ_lib IN ARRAY a_circ_libs LOOP v_found := 0; v_seen := ''; - - open v_cursor NO SCROLL FOR - SELECT c.call_number, c.create_date, c.status, c.location - FROM asset.copy c - WHERE c.circ_lib = v_circ_lib AND NOT c.deleted - ORDER BY c.create_date DESC; + v_c_oldest := NULL; + + -- If the number of keys in v_results is greater than or equal to + -- the number of results we need for the current page, then only look + -- for records that are newer than items currently in v_results + SELECT COUNT(keys) FROM skeys(v_results) AS keys INTO v_key_count; + IF v_key_count >= p_limit + p_offset THEN + open v_cursor NO SCROLL FOR + SELECT c.call_number, c.create_date, c.status, c.location + FROM asset.copy c + WHERE c.circ_lib = v_circ_lib AND NOT c.deleted + AND c.create_date > v_oldest + ORDER BY c.create_date DESC; + ELSE + open v_cursor NO SCROLL FOR + SELECT c.call_number, c.create_date, c.status, c.location + FROM asset.copy c + WHERE c.circ_lib = v_circ_lib AND NOT c.deleted + ORDER BY c.create_date DESC; + END IF; LOOP -- This loop gets up to a page worth of copies from each circ lib, if that many exist for each. FETCH v_cursor INTO v_temprec; @@ -62,11 +79,20 @@ BEGIN v_found := v_found + 1; v_results := v_results || hstore( v_record::TEXT, v_temprec.create_date::TEXT ); + IF v_c_oldest IS NULL OR v_c_oldest > v_temprec.create_date THEN + v_c_oldest := v_temprec.create_date; + END IF; + EXIT WHEN v_found = p_limit + p_offset; END LOOP; CLOSE v_cursor; + -- Update oldest information based on oldest row added in current loop + IF v_oldest IS NULL OR v_oldest < v_c_oldest THEN + v_oldest := v_c_oldest; + END IF; + END LOOP; RETURN QUERY SELECT KEY::INT8, value::timestamptz FROM each(v_results) ORDER BY value::timestamptz DESC, KEY::INT8 LIMIT p_limit OFFSET p_offset; RETURN; @@ -80,4 +106,3 @@ create index copy_circ_lib_create_date on asset.copy (circ_lib, create_date); create index unit_circ_lib_create_date on serial.unit (circ_lib, create_date); COMMIT; - -- 2.11.0