When we select a date range more than about 5 days, the entire openEMR system becomes unusable. To recover, we require manual kill -9’s of multiple mysql processes and usualy have to completely stop/restart the service to regain functionality.
I spent a couple days last week researching the problem and found some things I consider to be alarming.
I added a query counter to library/sql.inc that gets zeroed out upon each instantiation of the billing report and counts the use of each of those query functions contained wtihin the library. Friday, I ran a report for only the current day, which resulted in 70 rows of information.
The query count was 898.
On cursory study, I found a set of 3 queries that were re-used over and over again with only the pid and encounter information getting updated. I also found repeated use of the same query “SELECT * from x12_partners WHERE id = ‘121595’”. This example was observed dozens of times.
I think there are some serious performance issues with the billing report and probably others that stem from the way sql is being used during the page creation process. Has anybody else experienced problems like this or are there any of you who are aware of this behavior?
Click -> to bring up the report. The file is interface/billing/billing_report.php
I think indexing will certainly help with the limited scalability of the tool but I’m still concerned that it takes so many individual queries to build something so relatively small.
Make sure you have indexes on billing.pid and form_encounter.pid. They should be there, but if you have upgraded from 2.8.3 or earlier they might not be.
If so, or if creating them does not help, try creating indexes on billing.encounter and on form_encounter.date. Let us know what happens.
I added indexes to all of the recommended columns. There is a noticeable speed up and we’re able to run a month’s worth of encounters on that report. It still breaks about about a month and a half but that’s definitely improvement.