Speed improvement for Encounters report

OpenEMR Development
1

garcianc wrote on Saturday, April 13, 2013:

When running the Encounters report, the system may take an extremely long time or it may even time out while waiting for the query to run. This happens in particular when running the report for a large date range (e.g. one year). Adding an index for encounter and date fields in the form_encounters table improved performance significantly.

Link to database.sql commit:

Link to 4_1_1-to-4_1_2_upgrade.sql commit:

2

bradymiller wrote on Sunday, April 14, 2013:

Hi,

Placed a comment here:

Another thing is that a datetime index seems expensive (basically will index every single entry since the datetime will always be different). Can you give us an idea of the performance gained by just adding an encounter index? And then the performance gained by adding the date index?

thanks,
-brady
OpenEMR

3

garcianc wrote on Sunday, April 14, 2013:

Here is the query that the encounters report was running for this test:
SELECT
fe.encounter,
fe.date,
fe.reason,
f.formdir,
f.form_name,
p.fname,
p.mname,
p.lname,
p.pid,
p.pubpid,
u.lname AS ulname,
u.fname AS ufname,
u.mname AS umname
FROM ( form_encounter AS fe, forms AS f )
LEFT OUTER JOIN patient_data AS p
ON p.pid = fe.pid
LEFT JOIN users AS u
ON u.id = fe.provider_id
WHERE f.encounter = fe.encounter AND f.formdir = “newpatient” AND fe.date >= “2012-04-10 00:00:00” AND fe.date <= “2013-04-10 23:59:59”
ORDER BY lower(u.lname), lower(u.fname), fe.date;

I just ran this query again with the following results:

  • Query time without either index (as-is) = 835.223s
  • Query time with encounter index = 5.31s
  • Query time with encounter and encounterdate index = 4.42s

The above query is for one year worth of data, which in our system returned 34,491 records.

Adding the date index did not have as dramatic an improvement, relatively (although it is about 20% faster). It shaves off a second or so, on average, off the query. So, if you want to only use the encounter index, that alone should help a great deal. Thanks.

4

garcianc wrote on Sunday, April 14, 2013:

By the way, getting rid of the lower() functions in the ORDER BY clause reduces the final query time even further, but I did not have time to go through and investigate what impact that might have on the rest of the report on the application side, so I concentrated my efforts on the database side. Reducing to less than 10 seconds from 15-20 minutes seemed enough of an improvement to make the report usable.

5

bradymiller wrote on Wednesday, April 17, 2013:

Hi Nelson,

Well, a drop from 835 seconds to 5 seconds seems worth an additional index :slight_smile:

Recommend a revised commit that just creates the encounter index (no date index) in the database.sql and upgrade sql file (see my comments in your pull request on github regarding the upgrade sql script).

thanks,
-brady
OpenEMR