Query fix suggestion

OpenEMR Development
1

Ran into an interesting problem on a production OpenEMR instance earlier this week, where the SQL server load starting creeping up, eventually tripling the normal average, and the UI response time cratered.
Analysis of the SQL logs revealed that the table queries generated in vitals_fragment.php were now taking an average of 14-20 seconds to complete, which was creating a cascading database overload effect and dragging the system performance down across the board.

Suggest making the following change on line 25 of …/interface/patient_file/summary/vitals_fragement.php -

//retrieve most recent set of vitals
$result=sqlQuery(“SELECT FORM_VITALS.date, FORM_VITALS.id FROM form_vitals AS FORM_VITALS LEFT JOIN forms AS FORMS ON FORM_VITALS.id = FORMS.form_id AND FORMS.pid = FORM_VITALS.pid AND FORMS.formdir LIKE ‘vitals’ WHERE FORM_VITALS.pid=? AND FORMS.deleted != ‘1’ ORDER BY FORM_VITALS.date DESC”, array($pid) );

The original version of that query processes the entire forms table, rather than specifying only vitals.
Since that query executes every time someone loads a patient, it can really pile up on a busy EMR.
In this case, the production database contains close to 7 millions forms records and nearly a half million Vitals records. Refining that query took the average SQL processing time from 15-20 seconds, to under 2 seconds every time a patient’s demographic summary is opened. Reviewing past releases, it looks like that particular vitals query has been basically unchanged since back in the version 4 days, so no surprise it eventually turned into a problem.
This might not present an issue for smaller low volume practices, but eventually as data accumulates over time it will affect almost any system that’s been in use long enough to gather a significant patient visit history.

2 Likes
2

I tried this on my sandbox server that doesn’t have much in the way of resources. I picked a specific patient that has many encounters. Before making the change it was 36 seconds to load the dashboard. After making the change it was 24 seconds.

To me, that’s a healthy enough percentage of time saving to go ahead and make the change on our production server.

@Penguin8R thanks for sharing that info.

3

There are more of these to be found & fixed.
The back end for this platform happens to be a dedicated SQL host with 24 CPU cores, 64GB of RAM, and all SSD/NVMe storage, but it was still struggling to keep up with ~250 users online at any given time during clinical hours. That one query change cut the system load to 1/3 of what it was previously, which helps, but doesn’t change the inevitable, so this environment will still have to be migrated to a database cluster to keep up.