So to again summarize the proposed indexes to add to database.sql (along with making an upgrade mechanism in the upgrade script to do this):
1. ‘pid’ and ‘type’ indexes on the ‘lists’ table
2. ‘pid’ index in the ‘form_vitals’ table
3. ‘pid’ index in the ‘forms’ table
4. ‘pid’ index in the ‘form_encounter’ table (only in the upgrade script)
5. ‘patient_id’ index in the ‘immunizations’ table
6. ‘patient_id’ index in the ‘procedure_order’ table
7. ‘pid’ index in the ‘pnotes’ table
8. ‘pid’ index in the ‘transactions’ table
9. ‘patient_id’ in ‘extended_log’ table (stores disclosures)
10. ‘patient_id’ in the ‘prescriptions’ table
So … unexplained issue now … If I don’t limit the query to under 3000 records it just goes off into outspace after about 3000 records have been read. No abort, no errorlog. Sometime it stops and presents a blank screen, sometime it just runs until I give up. I am monitoring the mysql processes and I can see that no queries are being executed anymore.
Normally it’s scrolling along looking like this …
| 2057 | emr_fmc_40 | localhost | emr_fmc_40 | Query | 0 | statistics | SELECT * FROM `lists` WHERE `type`='medical_problem' AND `pid`='3073' AND `diagnosis` LIKE '%ICD9:25 |
| 2057 | emr_fmc_40 | localhost | emr_fmc_40 | Query | 0 | | SELECT `weight` FROM `form_vitals` WHERE `weight`!='' AND `pid`='3098' AND `date` <= '2011-10-23 22: |
| 2057 | emr_fmc_40 | localhost | emr_fmc_40 | Query | 0 | statistics | SELECT * FROM `lists` WHERE `type`='medical_problem' AND `pid`='3128' AND `diagnosis` LIKE '%ICD9:64 |
| 2057 | emr_fmc_40 | localhost | emr_fmc_40 | Query | 0 | freeing items | SELECT * FROM `lists` WHERE `type`='medical_problem' AND `pid`='3140' AND `diagnosis` LIKE '%ICD9:25 |
Then, it just stops showing any new queries.
I had to increase max execution time for the default 60 secs to 240 secs to keep from getting these aborts:
[Sun Oct 09 18:41:17 2011] [error] [client 127.0.1.1] PHP Fatal error: Maximum execution time of 120 seconds exceeded in /opt/emr_fmc/openemr/library/log.inc on line 627, referer: http://localhost/fmc/openemr/interface/main/left_nav.php
[Sun Oct 09 21:09:01 2011] [error] [client 127.0.1.1] PHP Fatal error: Maximum execution time of 120 seconds exceeded in /opt/emr_fmc/openemr/library/adodb/adodb.inc.php on line 768, referer: http://localhost/fmc/openemr/interface/main/left_nav.php
Now a thought … when I ran this successfully, it produced 877 reminders on 2000 patients. No one will actually do followup on 877 reminders. I think we need a way to say … starting from today, or turn them all off clinic wide to begin with and activate them one patient at a time or using some criteria like “seen this year”. Maybe I’m just not clear how it’s supposed to be used in the real world.
On this database I did nothing. upgraded to 4.1.0 and selected to run Adminstration->Patient Reminders so this is the default behavior.
Lets ignore above issue (the mysql breaking at 3000 and the real world stuff) for a sec. Can you place the indexes I just posted above in 21(about 10 of them now ) and run it through 2000 patients on the script to see how long it takes. Also, can you see if this drastically speeds up the patient summary page (especially the Clinical Reminder and Patient Reminder widgets).
You are correct, I double checked this and I had enabled Adult Weight Screen and A1C. I turned them both of and it completed, with all 4500 records. No reminders to report of course. Frankly it should have just said “Hey, your don’t have any turned on, dummy!” and quit immediately …
As to the indexes … I did do that and it does speed up the demographics widgets a lot. Maybe even enough to be good.
Now … I turned on Adult Wt screening only and it ran on the full set and produced results in < 5 mins
Hypertension ran significantly slower… orders of magnitude and failed to complete, blank screen after appx 2500 records read.
Afraid I can’t leave Real Use behind, I have a customer who expects me to have a answer (or a least a plan) in the morning to address his real use of this, or he needs to buy a tool to do reminders with.
This is what killed the HTN report … PHP Fatal error: Maximum execution time of 240 seconds exceeded in /opt/emr_fmc/openemr/library/adodb/adodb.inc.php
set time limit worked. It’s kind of a kludge, need to figure out what is causing the time out, but it worked. HTN test completed in 15 mins or a bit less.
Tested it with HTN, A1C, PSA and Mammograms on, took - 25 minutes, not terrible if the screen actually showed that it was processing while you waited.
For the processing screen issues recommend making a div element on the page that contains the ajax loading indicator and shows it at start of script. Then at end of script, hide it. This should work well; note can’t simply do a php message, because it will be cached until the entire script is done.
Still likely coding/optimization work to be done in the diagnosis filter/target stuff in the CDR engine. If you did all the diabetes rules, may take a really long time.
But at least we’ve gained some major improvements on the patient summary screen widgets. Am curious how Sam’s mega-database patient summary screen (with CDR engine turned on) will fare with these sql indexes.
Another option for the large databases is to create a php command line script that has this cron’d to do daily. The CDR stuff is modular enough to do this very easily.
As for starting with ajax, I recommend using jquery post. http://api.jquery.com/jQuery.post/
Think of it as doing a post normally, but instead of the browser display being updated immediately, after the post returns, it calls the callback function that you specify (which is where you update the browser.)
As an aside, having the database do a separate query for each patient and for each reminder type seems horribly inefficient. There significant overhead in each sql call. For example,
rather than iterating through each patient one at a time and determining if they have diabetes with
SELECT * FROM `lists` WHERE `type`=‘medical_problem’ AND `pid`=‘167’ AND `diagnosis` LIKE '%ICD9:250
It would be much faster to determine the list of all patients with diabetes in one query and work off of that.
select PID from ‘lists’ WHERE `type`=‘medical_problem’ AND `diagnosis` LIKE '%ICD9:250
The fact that this piece of code does hundreds/thousands of queries suggests to me that there is a better way to accomplish the same task by using joins in the database and letting MySQL do more of the heavy lifting (scanning data, which is what relational databases are good at.) rather than letting PHP(interpreted, designed for generating web pages) work so hard generating and loading queries.
Just another thought. I have a batch process which scans ICD-9 codes that I’ve been trying to performance tune. When I try to run it all in one batch, it slows down to a crawl towards the end of the run because it seems that PHP hangs on to system resources that it used for the early tasks which it no longer really needs.
When I changed the process to run 100 entries at a time (it’s a php command line process) it finishes in under 20 minutes, where as before the process was taking the better part of a day to complete exactly the same task.
Providing a mechanism to “chunk” the patient reminders into smaller sets of data to process at a time might go a long way to improving performance.
Alternately, there might be other interesting ways to subset the data. So for example, only run the patient reminder queries on the patients who are scheduled to be seen in the coming week or some other chronological mechanism.
Still getting the white screen in Patient Reminders on sites with large patient tables (I have one with >21000 patients, another with >78000 patients, for example). Will the set time limit workaround help with these? Is it going to be a problem to upgrade to 4.1 if the reminders are not going to be utilized right away?
The time limit change didn’t have any effect, it ran for over an hour, then back to the empty white frame.
I need to start on 4.1 upgrades, so I’m just going to tell people not to mess with reminders for now, since it still works when you open a patient it’s not a really big deal for the moment.
) and run it through 2000 patients on the script to see how long it takes. Also, can you see if this drastically speeds up the patient summary page (especially the Clinical Reminder and Patient Reminder widgets).