mdsupport wrote on Thursday, September 02, 2010:
This observation relates to 3.2.0. Not sure if any changes have been incorporated in the upcoming release.
In the Batchcom page, the mySQL query results in horrible execution plan. Few simple changes improve the performance dramatically. After some optimization, this will be a great candidate for view(s) but the package seems to avoid database views completely. Like stored procedures, are views to be avoided as well?
Here is one example:
SELECT DISTINCT patient_data.*
, MAX( cal_events.pc_endDate ) AS last_ap
, MAX( forms.date) AS last_visit
, (DATEDIFF(CURDATE(),patient_data.DOB)/365.25) AS pat_age
FROM (patient_data, forms) LEFT JOIN openemr_postcalendar_events AS cal_events
ON patient_data.pid=cal_events.pc_pid
LEFT JOIN forms AS forms2
ON patient_data.pid=forms2.pid
WHERE cal_events.pc_eventDate > ‘2010-09-01’
AND cal_events.pc_endDate < ‘2010-09-06’
AND patient_data.email IS NOT NULL
GROUP BY patient_data.pid ORDER BY patient_data.lname
Changing this to the following provides a decent online response …
SELECT patient_data.*
, (SELECT MAX( openemr_postcalendar_events.pc_endDate ) AS last_ap
FROM openemr_postcalendar_events
WHERE pc_pid = patient_data.pid
AND pc_eventDate > ‘2010-09-01’
AND pc_endDate < ‘2010-09-06’ )
, (SELECT MAX( forms.date) AS last_visit
FROM forms
WHERE forms.pid = patient_data.pid)
, (DATEDIFF(CURDATE(),patient_data.DOB)/365.25) AS pat_age
FROM patient_data
WHERE patient_data.email IS NOT NULL
GROUP BY patient_data.pid ORDER BY patient_data.lname