Improve Reporting - Reminders query performance
Description
Environment
None
Attachments
2
- 12 Dec 2022, 05:02 AM
- 12 Dec 2022, 04:52 AM
Activity
Show:
Fixed
Details
Details
Assignee
Tony De Keizer
Tony De KeizerReporter
Tim Anderson
Tim AndersonTime tracking
5.5h logged
Components
Fix versions
Affects versions
Priority
MinorWho's Looking?
Open Who's Looking?
Created December 12, 2022 at 4:24 AM
Updated March 2, 2023 at 10:24 PM
Resolved December 12, 2022 at 7:10 AM
Who's Looking?
On large databases, the Reporting - Reminders - Pending tab performs poorly.
The SQL from which the following is derived ends up doing a table scan on entity_relationships (patient_owner) below. This is an odd choice by the query optimizer, but we don’t have a facility to specify an index hint via the ArchetypeQuery API.
SELECT * FROM acts reminder_item INNER JOIN act_relationships r_items ON reminder_item.act_id = r_items.target_id INNER JOIN acts reminder ON r_items.source_id = reminder.act_id INNER JOIN participations ppatient ON reminder.act_id = ppatient.act_id INNER JOIN entities patient ON ppatient.entity_id = patient.entity_id JOIN entity_relationships patient_owner ON patient_owner.arch_short_name = 'entityRelationship.patientOwner' AND patient.entity_id = patient_owner.target_id AND (patient_owner.active_end_time IS NULL) INNER JOIN entities customer ON customer.entity_id = patient_owner.source_id AND customer.arch_short_name = 'party.customerperson' AND customer.active = TRUE WHERE (reminder_item.arch_short_name = 'act.patientReminderItemPrint' OR reminder_item.arch_short_name = 'act.patientReminderItemEmail' OR reminder_item.arch_short_name = 'act.patientReminderItemExport' OR reminder_item.arch_short_name = 'act.patientReminderItemList' OR reminder_item.arch_short_name = 'act.patientReminderItemSMS') AND reminder_item.activity_start_time < '2022-11-25' AND reminder_item.status IN ('PENDING') AND r_items.arch_short_name = 'actRelationship.patientReminderItem' AND reminder.arch_short_name = 'act.patientReminder' AND reminder.status = 'IN_PROGRESS' AND ppatient.arch_short_name = 'participation.patient' AND ppatient.activity_start_time < '2022-11-25' AND patient.arch_short_name = 'party.patientpet' ORDER BY customer.name ASC, customer.entity_id ASC, reminder_item.arch_short_name ASC, patient.name ASC, patient.entity_id ASC, reminder_item.activity_start_time ASC, reminder_item.act_id ASC LIMIT 80Adding an index on entity_relationships means that the query optimizer no longer selects it, and the query performance improves:
alter table entity_relationships add index (arch_short_name, target_id);