Encounter.php sucks

Day-to-Day Users
1

drbowen wrote on Thursday, March 31, 2011:

It would be great if the “current” encounter auto selected the last / most recent encounter instead of having to “list encounters.”  This would save a lot of time.  Every time I log into a new computer I  have to List encounters, then select todays encounter.  The encounter.php script is so incredibly inefficient it usually takes 15-20 seconds to load.  I usually stop, go do something else for awhile, and then come back later to click today’s encounter.  Evey time I leave the room, I dutifully log out, I come back in and have to go back through the same process of selecting “list encounters” then click today’s visit, then finally get back to work.  I have to do this typically 3-4 times per visit.

The indexes for encounter.php speed up the above process 3 fold (from 45-60 seconds to only 15-20 seconds) should be incorporated into  the main branch.

Sam Bowen, MD

2

jcahn2 wrote on Thursday, March 31, 2011:

Ahoy Sam,

Our workstations run win XP (don’t ask).  The screen saver allows us to lock the screen using menu+L each time we leave the room.  A password unlocks the screen and we return exactly where we left off.  I haven’t tried this in Kubuntu, or any other flavors of linux, Mac, or Win, but I suspect there is a screen locking shortcut for all of them.  This might save you a few hairs on your head.   I do like the idea of having the default screen be the current encounter when a clinician enters the chart.     Jack

3

mike-h30 wrote on Thursday, March 31, 2011:

Dr. Bowen,

I feel your pain.   Our patient’s encounter summary lists have grown tremendously since OpenEMR 2.8.3.  All of my users complain about the load time for the encounters list.    I am thinking about using the encounter summary frame less by disabling the bottom frame and using  the new “Encounter History” drop down at the top of the screen in OpenEMR 4.0 instead.  The “Encounter History” drop down seems to load much faster than the encounter summary frame.

-Mike

4

drbowen wrote on Thursday, March 31, 2011:

Dear Jack:

Linux has this feature also but I guess I am just a little more paranoid than you.  I like to log out and lock the screen.

The “encounter.php” has many “features” that are great when you have a small database.  By the time the database gets to the size that we have, this query takes a long time to run and becomes very tiresome.

Thanks for listening to me.

Sam Bowen, MD

5

drbowen wrote on Thursday, March 31, 2011:

Usually, Jeremy Wallace indexes the tables involved.  This speeds the query up three fold. He then modifies encounter.php to the old behavior of returning only the first 15 results.  These changes make the encounter.php dramatically easier to deal with but alas he has been tied up trying to solve the Meaningful Use stuff.

Sam Bowen, MD

6

aethelwulffe wrote on Thursday, March 31, 2011:

I should hope we are using practices that make efficient use of bandwidth and processors.  I might ask, if 20 seconds are passing, are you using a LAN address or localhost, and are you on a Gigabit backbone?

I hate to be a “you outta” type, but really, instead of logging in and out 5 times, perhaps you should save yourself some time and frustration and do a simple smart card (CAC/PKI like the Dept of Defense or just a browser certificate).  It really would not cost too much, the cards can act as ID’s and can even be used for door access and such.    The WindPC Nettops I installed in both the Electrophysiology clinic and the Mental Health agency both had SC readers on them already.  It was a couple of years before I got around to it, but making the cards and setting up a Single Sign On style access using Windows Authentication was very simple.  Something similar can be done with Kerberos.
  I am looking at using a smart card chip as a relay controller just to act as an on-off controller for a modified KVM switch.  This way, you can stay logged on, but the keyboard, video and mouse are disabled if you pull your card.  This will allow me to hide the computer cases (again).

7

yehster wrote on Thursday, March 31, 2011:

Dr Bowen,

Do you “feel the pain” with all patients in your system (even new ones with only a few encounters?) or is it only with patients with large numbers of encounters?

Any chance you help identify the specific bottlenecks?  If you can either run xdebug on your server and get profiling information to share, or if you can obfuscate your patient data in your database and share that to the community as test data it would go a long way towards addressing this problem, and making sure it doesn’t come back in future versions.

Also to clarify: Is this the script thats running slowly?
/openemr/interface/patient_file/history/encounters.php
(encounters with an s!)

The code to limit results is still in that file, but it’s commented out.

        /*************************************************************
        // $count++; // Forget about limiting the number of encounters
        if ($count > $N) {
            //we have more encounters to print, but we’ve reached our display m$
            print “<tr><td colspan=‘4’ align=‘center’>” .
                "<a target=‘Main’ href=‘encounters_full.php’ class=‘alert’ oncl$
                xl('Some encounters were not displayed. Click here to view all.$
                “</a></td></tr>\n”;
            break;
        }
        *************************************************************/

@aethelwulffe,  I am fairly certain that this issue is primarily database problem, and not a network bandwidth problem.   The script does a bunch of queries within a for loop that is going to make the code run slower linearly with the number of encounters.

8

yehster wrote on Thursday, March 31, 2011:

I am also assuming that your issue is with the recently released 4.0 code?

Of course just uncommenting the count++ line won’t work completely as the referenced “encounters_full.php” doesn’t seem to exist.  However, if limiting the results is all it takes to fix your problem performance issue it would be a quick and easy fix.

9

mike-h30 wrote on Thursday, March 31, 2011:

yehster,

It is definitely a DB issue.  We have some patients with 400 plus encounters and the encounters summary page takes a long time to load for these  patients versus instantly for new patients with very few encounters.   Viewing past and current encounters for these patients is painstaking!  The “Encounter History” drop down in OpenEMR 4.0 helps tremendously when viewing past encounters for these patients with 400 plus encounters in our system.   Also, the code is not in the encounters.php file of OpenEMR 4.0.

Also to clarify: Is this the script thats running slowly? /openemr/interface/patient_file/history/encounters.php (encounters with an s!) The code to limit results is still in that file, but it’s commented out. /************************************************************* // $count++; // Forget about limiting the number of encounters if ($count > $N) { //we have more encounters to print, but we’ve reached our display m$ print “<tr><td colspan=‘4’ align=‘center’>” . "<a target=‘Main’ href=‘encounters_full.php’ class=‘alert’ oncl$ xl('Some encounters were not displayed. Click here to view all.$ “</a></td></tr>\n”; break; } *************************************************************/

Mike

PS - This SourceForge forum needs a preview button like other forums.  Its a pain using the scroll bar to proof my post.  Kinda like vieiwng a PDF in the smaller document viewing window of OpenEMR 4.0.  :slight_smile:

10

drbowen wrote on Thursday, March 31, 2011:

The LAN that has 10 gigabit cat 6 ethernet cables, 1 gigabit switches, and 1 gigabit ethernet cards on the production server. 

My main workstation has twin Phenom 64 bit chips with  2 gigabytes of ram, and a video card that runs 256 megabytes of memory (512 in Windows). Ubuntu 10.1 patched up to current.

The server has 8 gigabytes of RAM, quad 64 bit Opteron chips Ubuntu 10.1 patched up to current. There are 4 Seagate Barracuda (7200 RPM I think) x 1 terabyte hard drives in a RAID 5 configuration with 3 terabytes of working hard drive space.

My version is actually 4.0 dev-tip from about March 14th.

The encounter.php length of time to complete is clearly related the length of time the patient has been coming to the office and how many times a year they come to the office.  So if they justed started coming to the office and there is only one visit this is lightning fast.

As the time of belonging to the practice increases and as the number of office visits increases the length of the query gets longer and longer.  Since I am the senior member in my group and many of my patients have been coming since we started on OpenEMR in the fall of 2003, I have a large number percentage wise of patients that have visits all the way back to 2004.  20 visits per year x 7 years = 140 results.

Jeremy Wallace measured how many times the encounter.php script accesses the database to complete the page.  He measured 900 individual accesses to the database to complete the page on one of these lengthier queries.

The old behavior of encounter.php was to limit the number of results returned to about 15.  This was the behavior I believe in version 3.0.1.  This was changed to return all results at least in 3.2 and also in 4.0.  As far as I know none of the versions have the indices built to help the query run faster.

I work on a very fast net-work, with a fast server with lots of resources, at a fast work station.  I understand how to make it easier for me to access individual work stations.

The reason I started this thread is that I don’t think it is reasonable for this query to be accessing the database 900 separate times and taking 45-60 seconds (unmodified behavior in version 4.0.0).  I posted this because I think this cuts into my productivity, it will affect the productivity of every practitioner  that uses OpenEMR, and I think this query needs to be fixed.  At the very least the main distribution ought to have the tables indexed.  Tony McCormick suggested that turning the length the query back and forth to “15 results” or “all results” could be configured in the globals so that the end user could select which length they prefer.

Samuel T. Bowen, MD
http://www.oemr.org

11

drbowen wrote on Thursday, March 31, 2011:

openemr/interface/patient_file/history/encounters.php?billing=0&issue=0

It does appear to “linear based on the number of encounters.”

Sam Bowen, MD
http://www.oemr.org

12

bradymiller wrote on Thursday, March 31, 2011:

Hi,
How about having the following options at the top encounter listing:
Show Last 6 Months…
Show Last Year…
Show Last 2 Years…
Show All

With the default set to Show Last 6 months. (this is what I’ve seen on some EMR’s which seems to work out well).

-brady

13

yehster wrote on Thursday, March 31, 2011:

I was looking at a December version of the code. 
Dr. Bowen,
In the 4.0 tip. You could try adding this simple line to your encounters.php

$query .= " LIMIT 15";

around line 256
after

$query .= "LEFT JOIN users AS u ON u.id = fe.provider_id WHERE fe.pid = ? " .
  “ORDER BY fe.date DESC, fe.id DESC”;

Nested loops for queries like is done in this file is not a scalable approach. 

The formal fix which allows for configuring the max number of rows retrieved and for allowing the option to be configured does not seem to be that difficult.  But like others, I have other issues on my plate.

14

rnagul wrote on Thursday, March 31, 2011:

I know I am a newcomer here, but to improve the performance I suggest the following:

MySQL runtime settings, what are they set to? I am assuming to the huge settings based on your RAM and CPU values, if not try setting these values.
Next, we need to examine the query itself, a lot can be improved by proper SQL
Finally, the schema itself, adding indices, normalizing the data, etc

Ramesh

15

stephen-smith wrote on Thursday, March 31, 2011:

There’s been some resistance in the past to normalizing the database.  However, if it (a) doesn’t break anything and (b) is backed by some benchmark improvements, it would be nice.

16

yehster wrote on Thursday, March 31, 2011:

The fundamental performance issue involved here CANNOT be addressed simply by tuning the database schema (indicies, normalization). It’s not that there is one “runaway query.” The problem is that for every encounter returned, there is at least one additional query to the database to get the forms associated with the encounter. So the examples where these guys have 400 encounters for a patient, there are at least 401 queries to the DB. 

The bottleneck is that iterating over the 300+ lines of code in the while loop which hits the database one or more times each pass is just plain going to take a lot of time if you do it 400 times. 

17

drbowen wrote on Sunday, April 03, 2011:

I am not sure but encounters.php has a loop that queries for each of the forms.  Most encounters have three forms encounter (chief complaint, vital signs, the office visit form), but the script also fetches billing and insurance information, and then all the documents.  I am not sure but I think the loop does 6 database queries per office visit.  If there are 200 office visits for a particular patient, then the database is queried 800-1200 times.  In database completely one form requires:

forms 300,000 records
form_encounters 96,000 records
form_vitals 76,000 records
form_dictation 98,000 records

If I turn on tool tips, the script has to return the text from 200 records in Form_dictation with an average of about 65 characters per page.  Lots of pages are shorter, lots of encounters are three pages.

I personally don’t think that this is the place to be looking up documents, billing information, and insurance information. 

The only reason all of the forms are returned is because a few practitioner like the feature of letting the mouse cursor float over the form so they can read the text inside.  I have tool tips turned off by default because the performance is even worse with the tool tips turned on. 

If I need to actually look at the older visits, I always use the “report” section because it is much faster and it is easier to figure out which visits need to be looked at.

Changing the query to a cross table query with forms returning only the forms.formname=“New Patient Encounter”, Forms.date, and form_encounter.reason  would provide the required the required function produce a dramatically faster query. 

It would be better if the forms.formname was not the field that needed to be searched since this is a text field. Indexing this field would help.

Perhaps encounters.php could have both queries that are configurable one “traditional behavior” the other turn on “fast encounters.php”.

Sam Bowen, MD

18

drbowen wrote on Sunday, April 03, 2011:

I like Brady’s idea of cutting off at 6 months.  This seems a reasonable compromise for a short term fix.

19

sunsetsystems wrote on Sunday, April 03, 2011:

I would rather see paging than truncation.  I think that would be the normal expected behavior.  The services list is a good example of it.

Rod
www.sunsetsystems.com

20

jenjhall wrote on Sunday, April 03, 2011:

I know that there is difficulty in making oemr suitable for all worlds but a cut off that includes 3 months would be nice for physical therapy practices where clients are often seen 2 or 3 times per week and over an extended time.