I have posted an updated version of Batchcom for testing and review, some comments still need to be removed. What was done was to clean up the layout of the screen and most importantly redo the SQL so the it would actually be able to complete on a large database. All tests up till now hung indefinitely on large databases.
The actual coding was done by Visolve, MI2 just paid for it…
Please review and more importantly test it on large systems…
Visolve tested in on a large live database and I tested on a different one. It return lots of results, but there are lots of options too. Hence the need to have feedback from others. I’ve got a third test going with customer of mine, as well.
-Tony
Old query which is based on LEFT JOINS, selected the matched rows along with unmatched rows of
left tables. So we modified the query as to select only the matched rows based on WHERE condition.
Also, we added one composite index to speed up the where conditions search in the query.
(a) Performance Tuning:
Old query:
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 > ‘<<date>>’ AND cal_events.pc_endDate < ‘<<date>>’ AND
forms2.date > ‘<<date>>’ GROUP BY patient_data.pid ORDER BY patient_data.lname;
Perf tuned query:
select 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
join openemr_postcalendar_events AS cal_events on patient_data.pid=cal_events.pc_pid join
forms on patient_data.pid=forms.pid WHERE cal_events.pc_eventDate > ‘<<date>>’ AND
cal_events.pc_endDate < ‘<<date>>’ AND forms.date > ‘<<date>>’ GROUP BY patient_data.pid
ORDER BY patient_data.lname;
Added index:
ALTER TABLE openemr_postcalendar_events ADD INDEX composite_idx(pc_eventDate,pc_endDate);
Result Comparison:
Result set: 693 rows
After additional index: 693 rows
Old Query - retrieve time
11 min 32.36 sec
11 min 31.23 sec
It looks like patients that either do not have an appt or do not have an encounter will be skipped. This was not the case before the modification. Is it ok to do this; I don’t really know the practical use of this script well enough to answer this. thoughts?
I fwd’d your note to Visolve (in case they didn’t see it here).
The script, in my opinion should be flexible enough to do what ever the inputs seem to allow. :-) One version that would seem to be viable would be a list of patients that haven’t ever been seen at all I guess. Though normal use would be for patients you have seen or will see.
Mind you, we have a large db, and have never been able to use this (just hangs the whole damn system), so I don’t fully know what I am talking about, but the common situation in the instances I have installed is that scheduling is only used in a spotty manner, yet there are still notifications that need to go out. An example would be where we need to verify emergency contact info on an annual basis for someone we MIGHT serve. I bet that folks running a “boutique” style clinic might have similar unscheduled patients that they need to contact.
We have modified the batchcom as per the suggestion given in http://github.com/tmccormi/openemr/commit/67be3752fd0215960183890ffce32299d0768d for our previous commit. Currently the batchcom tool will retrieve patient data and future appointments (with a row of data for each future appointment) along with last appointment date and last visit dates.