Details
-
Bug
-
Status: Closed (View Workflow)
-
P3
-
Resolution: Cannot Reproduce
-
None
-
None
-
0
-
Folijet Support
-
Morning Glory (R2 2022)
-
Not a bug
Description
Overview:
While investigating bottlenecks for single-record OCLC data import, it was found that the mod-storage API
/source-storage/source-records?snapshotId={snapshotId}
takes over 3 seconds and at times averages around 3.5 seconds to return. Investigating this issue further, it looks like the problem is in the SQL query and specifically the ORDER BY clause adds 3+ seconds into the execution.
with cte as (with cte as ( select count(*) from <tenantId>_mod_source_record_storage.records_lb where ( <tenantId>_mod_source_record_storage.records_lb.snapshot_id = cast('21c419bf-9225-4d54-b9ae-d98e6d1bd10a' as uuid) and <tenantId>_mod_source_record_storage.records_lb.state = 'ACTUAL'::<tenantId>_mod_source_record_storage.record_state and <tenantId>_mod_source_record_storage.records_lb.record_type = 'MARC_BIB'::<tenantId>_mod_source_record_storage.record_type and <tenantId>_mod_source_record_storage.records_lb.leader_record_status is not null)) select <tenantId>_mod_source_record_storage.records_lb.id, <tenantId>_mod_source_record_storage.records_lb.snapshot_id , <tenantId>_mod_source_record_storage.records_lb.matched_id , <tenantId>_mod_source_record_storage.records_lb.generation , <tenantId>_mod_source_record_storage.records_lb.record_type , <tenantId>_mod_source_record_storage.records_lb.external_id , <tenantId>_mod_source_record_storage.records_lb.state , <tenantId>_mod_source_record_storage.records_lb.leader_record_status , <tenantId>_mod_source_record_storage.records_lb.order, <tenantId>_mod_source_record_storage.records_lb.suppress_discovery , <tenantId>_mod_source_record_storage.records_lb.created_by_user_id , <tenantId>_mod_source_record_storage.records_lb.created_date, <tenantId>_mod_source_record_storage.records_lb.updated_by_user_id , <tenantId>_mod_source_record_storage.records_lb.updated_date , <tenantId>_mod_source_record_storage.records_lb.external_hrid, <tenantId>_mod_source_record_storage.marc_records_lb.content, count from <tenantId>_mod_source_record_storage.records_lb left outer join <tenantId>_mod_source_record_storage.marc_records_lb on <tenantId>_mod_source_record_storage.records_lb.id = <tenantId>_mod_source_record_storage.marc_records_lb.id right outer join (select * from cte ) as alias_80949780 on 1 = 1 where ( <tenantId>_mod_source_record_storage.records_lb.snapshot_id = cast('21c419bf-9225-4d54-b9ae-d98e6d1bd10a' as uuid) and <tenantId>_mod_source_record_storage.records_lb.state = 'ACTUAL'::<tenantId>_mod_source_record_storage.record_state and <tenantId>_mod_source_record_storage.records_lb.record_type = 'MARC_BIB'::<tenantId>_mod_source_record_storage.record_type and <tenantId>_mod_source_record_storage.records_lb.leader_record_status is not null) order by <tenantId>_mod_source_record_storage.records_lb.id asc limit '10'
Without the ORDER BY clause it takes a little over 100ms to run the entire query, but with it, the query returns after 3+ seconds.
This call is used in the single-record OCLC data import workflow where is made periodically (every 500ms) to check on the status of the import.
(Note that fixing this problem does not improve single-record OCLC data import because despite this call taking 3.5s to return, mod-copycat calls it twice, which implies that DI takes at least 7s to finish. Fixing this issue may open a flood gate of these calls blasting the DB. Need to fix DI performance for single-record imports first.)
Steps to Reproduce:
- Log into some FOLIO environment and invoke the API with an appropriate snapshotID
/source-storage/source-records?snapshotId={snapshotId}
Expected Results:
- API call returns quickly under a second.
Actual Results:
TestRail: Results
Attachments
Issue Links
- defines
-
UXPROD-3464 NFR: Data Import R2 2022 Morning Glory Support Bug work
-
- Closed
-
- has to be done after
-
-
- Closed
-
-
-
- Closed
-