drbowen wrote on Wednesday, August 01, 2012:
I am not sure if this qualifies as a bug or not but when we recently upgraded from 4.1.0 to 4.1.0 patch 11 we noted that the form_vitals is taking much longer to execute than before the upgrade. Is there any indexing that should be applied to this form possibly on the pid field?
Sam Bowen, MD
OEMR
drbowen wrote on Wednesday, August 01, 2012:
Previously this executed very quickly, possible just 1-2 seconds but not the query takes 14-15 seconds to execute.
Software versions
Ubuntu current
mysql 5.1.61-0ubuntu0.10.10.1 (Ubuntu)
apache2 2.2.16-1ubuntu3.5
PHP 5.3.3-1ubuntu9.10 with Suhosin-Patch
hardware:
ASUS quad opteron
8 Gigabyte RAM
Sam Bowen, MD
OEMR
sunsetsystems wrote on Wednesday, August 01, 2012:
What action exactly is taking a long time?
drbowen wrote on Wednesday, August 01, 2012:
Once we have started an encounter we normally use the the “clinical” drop down box to select the forms that we want to use.
In the drop down box we click on form_vitals. It takes 14-17 seconds for the form to be displayed on the screen. This has the blank form on the left and all the historic vitals displayed to the right.
After entering data into the form_vitals and saving the data, if we need to reopen the form by clicking on the edit button:
The form takes 14-17 seconds before it displays on the screen. The recently entered vital sign data on the left and all the historic vital signs to the right.
Currently we have 315,550 records in the form_vitals table.
Sam Bowen, MD
OEMR
yehster wrote on Wednesday, August 01, 2012:
I suspect that this commit
https://github.com/openemr/openemr/commit/5bb5bffd9dd04f0632bdd01ee0f62e4982492ebd#interface/forms/vitals/C_FormVitals.class.php
is the source of your performance issues. It adds an additional join with the form_encounter table
Indicies on the encounter columns in forms and form_encounter tables might help.
sunsetsystems wrote on Wednesday, August 01, 2012:
I’d suggest adding an index on form_vitals.pid and perhaps also on forms.form_id.
yehster wrote on Wednesday, August 01, 2012:
Rod, The indicies you suggest should already exist.
I suspect that the new composite pid_encounter index you added:
https://github.com/openemr/openemr/commit/99fb6cff7c11090d5ff86ef0b6c9cd1075e7f4bc#sql/database.sql
in 4.1.1 would likely also address Dr. Bowen’s issue.
sunsetsystems wrote on Wednesday, August 01, 2012:
They are not on the 4.1.0 demo site at http://demo.open-emr.org:2100/openemr/ . Sam, are they present on your site?
drbowen wrote on Wednesday, August 01, 2012:
Currently I am showing Indexes on colums ** id, pid** on forms, form_encounters, form_vitals
Sam Bowen
sunsetsystems wrote on Wednesday, August 01, 2012:
OK try creating an index for form_id on the forms table. And as Kevin suggested, indexes for encounter on forms and form_encounter. Do them one at a time so we know which one matters the most.
yehster wrote on Wednesday, August 01, 2012:
Since the column form_id in forms has an informal “foreign key” relationship with all of the id column form_* tables, indexing that column probably makes sense even outside the context of this issue.
drbowen wrote on Wednesday, August 01, 2012:
Wooah….!
Paydirt!
I added
form_id INDEX 188210 form_id
and now the table is lightning fast again. Much less than 1 second. Technically the form comes up / blinks faster than I can measure.
Sam Bowen, MD
yehster wrote on Wednesday, August 01, 2012:
Glad to hear it. You might even notice speed up for other forms. I suspect that adding an index on “encounter” would have brought your system’s “old performance” back (1-2 seconds). However the code routinely joins form_* id with forms form_id, it needs to tablescan without the index.
drbowen wrote on Wednesday, August 01, 2012:
Do you guys want me to try the other indices one at a time?
Not sure, but can I remove the the form_id to allow testing of the other two indices?
Sam Bowen, MD
sunsetsystems wrote on Wednesday, August 01, 2012:
I think we’ve found the answer but you could if you like.
bradymiller wrote on Saturday, August 04, 2012:
Hi,
Just committed this fix to sourceforge (both the master and rel-411 branches).
-brady
OpenEMR
