Update BatchCom

tmccormi wrote on Friday, April 20, 2012:

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…

https://github.com/tmccormi/openemr/commit/c6447ed963c1196466557ef1f48fdb312a930bfb

-Tony

yehster wrote on Friday, April 20, 2012:

Have you tested it?  I only one patient result back with the new query.

tmccormi wrote on Friday, April 20, 2012:

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

tmccormi wrote on Friday, April 20, 2012:

Also they provided this information:

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

New Query - retrieve time
0.42 sec
0.36 sec

bradymiller wrote on Wednesday, April 25, 2012:

hi Tony,

Placed review on your second revision commit on github:
http://github.com/tmccormi/openemr/commit/67be3752fd0215960183890ffce32299d0768d5f

thanks,
-brady
OpenEMR

tmccormi wrote on Friday, April 27, 2012:

Batch communication tool:V3 - performance tuned - new branch

So, I’m getting out of the middle of this review process…  Here the next iteration.

Hello Tony,

We have updated our local openemr git with the latest dev code and
committed the Batchcom updates to it and have push the same to our repo.

Ref:
https://github.com/devi-visolve/Openemr/commit/5db2c4c6a43508e5ab3a64d8daab305a2aabb2ca

Please do review the same.

Thanks
DEVI.G

bradymiller wrote on Friday, April 27, 2012:

Hi,

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?

-brady
OpenEMR Project

tmccormi wrote on Saturday, April 28, 2012:

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. 

-Tony

bradymiller wrote on Saturday, April 28, 2012:

Hi,
The problem then may be “patients you will see” since it would skip patients that have a future appt, but do not yet have an encounter.
-brady

aethelwulffe wrote on Tuesday, May 01, 2012:

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.

aethelwulffe wrote on Tuesday, May 01, 2012:

BTW Tony, thank you very much for working (err paying) on this.

bradymiller wrote on Tuesday, May 15, 2012:

Hi,

Here’s the next revision, below was emailed to me from Visolve:
We have committed the updated batchcom tool to our git http://github.com/visolve-openemr/Openemr/commit/1b9833a355d3b2f178e9bc34cd3c03311f9548f3

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.

Please do share your views.

bradymiller wrote on Thursday, May 17, 2012:

Hi,
Code looks good and testing well, so committed this to sourceforge.
thank you for the contribution MI2 and Visolve,
-brady
OpenEMR