[MODCAL-51] Performance of checking out items degrades drastically when service point calendars defined for years into the future Created: 10/Oct/19  Updated: 15/Oct/19  Resolved: 11/Oct/19

Status: Closed
Project: mod-calendar
Components: None
Affects versions: None
Fix versions: None

Type: Bug Priority: TBD
Reporter: Christopher Creswell Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: performance
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Duplicate
duplicates MODCAL-50 Add indexes to improve /calendar/peri... Closed
Sprint:
Development Team: Vega

 Description   

I defined open hours for 4 service points in Lehigh's FOLIO test instance and extended them out to 2030 because we do loans that are a year long. In testing circulation, I found that it was taking almost a minute to checkout a single item. When I watched the database's logs, I found that the problem was with the query reproduced below, which is used to determine when the Library is open around the due date of the loan. I executed the query manually and observed that it took 50 seconds to finish with the calendars going out that far. I checked mod-calendar's actual_opening_hours table and found that it had ~16000 rows in it. I trimmed the calendars for the service points back to only extending out to 1/1/2021, leaving ~1800 rows in the actual_opening_hours table, and then it only took ~2 seconds to checkout an item. Here is the problematic query:

WITH openings_ids as (SELECT jsonb->>'id' opening_id FROM lu_mod_calendar.openings WHERE jsonb->>'servicePointId' = 'e89641e5-fa6f-45bc-a5a2-ba9f1f7c6baa'),closest_open_day as (SELECT aoh1.jsonb->>'actualDay' actual_day FROM lu_mod_calendar.actual_opening_hours aoh1 WHERE aoh1.jsonb->>'openingId' IN (SELECT opening_id FROM openings_ids) AND aoh1.jsonb->>'actualDay' > '2020-10-08T00:00:00.000+0000' AND aoh1.jsonb->>'open' = 'true' AND (SELECT count(_id) FROM lu_mod_calendar.actual_opening_hours aoh2 WHERE aoh2.jsonb->>'openingId' IN (SELECT opening_id FROM openings_ids) AND aoh2.jsonb->>'actualDay' = aoh1.jsonb->>'actualDay' AND aoh2.jsonb->>'exceptional' = 'true' AND aoh2.jsonb->>'open' = 'false') = 0 ORDER BY aoh1.jsonb->>'actualDay' asc LIMIT 1)SELECT jsonb FROM lu_mod_calendar.actual_opening_hours WHERE jsonb->>'openingId' IN (SELECT opening_id FROM openings_ids) AND jsonb->>'actualDay' = (SELECT actual_day FROM closest_open_day)

Perhaps an index is missing? Either that, or a more efficient way of finding the next available date the library is open is needed, because it's quite realistic for a library to define hours extending out for years.



 Comments   
Comment by Jakub Skoczen [ 10/Oct/19 ]

Kostyantyn Khodarev Khalilah Gambrell I've moved this to mod-calendar based on description – can you please investigate?

Comment by Hongwei Ji [ 11/Oct/19 ]

This was addressed in MODCAL-50 Closed , but apparently was not released.

Comment by Kostyantyn Khodarev [ 15/Oct/19 ]

Jakub Skoczen Should bugfix release be issues for mod-calendar with MODCAL-50 Closed fix?

Generated at Thu Feb 08 23:19:43 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.