openemr UuidMapping registry for vital records creates extremely slow API requests - PHP
Working with 100K of patients the UUID mapping for the vitals service (where a uuid is mapped to each individual column for each vital forms) is creating API responses that vary between 45 seconds to 1.5 minutes.
So it executes the following query for each of the 19 vitals observation codes which is what is creating the long delay. I'm looking at what can be done to optimize the query.
SELECT `form_vitals`.`uuid` FROM `form_vitals` LEFT OUTER JOIN `uuid_mapping` ON `form_vitals`.`uuid` = `uuid_mapping`.`target_uuid` AND `uuid_mapping`.`resource` = 'Observation' AND `resource_path` = 'category=vital-signs&code=3150-0' WHERE (`uuid_mapping`.`uuid` IS NULL OR `uuid_mapping`.`uuid` = '' OR `uuid_mapping`.`uuid` = '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0') AND (`form_vitals`.`uuid` IS NOT NULL AND `form_vitals`.`uuid` != '' AND `form_vitals`.`uuid` != '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0') LIMIT 1000
Seems like an odd query. Isn't that going to select all formvitals rows that have a uuid, up to the 1000 limit? Also, querying formvitals without including the forms table seems wrong. Forms can be deleted and forms.deleted is where that is indicated.
@sunsetsystems So it will select all of the formvitals records that do not have an associated uuid mapping table entry in the uuidmapping table. FHIR requires each vitals observation (bps, bpd, weight, etc) to have its own unique uuid that can be queried and retrieved. We store those in the uuid mapping table. Since we also report via FHIR whether the record has been deleted etc it doesn't matter here to check on the forms table.
I'd much rather we were running the uuid population code at the time of data insert rather than everytime the FHIR Vitals service is instantiated (which in turn slows down every vitals API request). I remember @bradymiller mentioning a reason why we did it that way, I just can't recall.
Note adding the following index drops the queries from 2.5 seconds per query down to .25 seconds. That still leaves API responses to being 13-17 seconds...
CREATE INDEX uuid_map_trgt_resources ON uuid_mapping(`target_uuid`,`resource`,`resource_path`);
Thanks for the clarification. An EXPLAIN might help for clues about optimizing.
@sunsetsystems Yea, I've been playing with it, EXPLAIN is what I've been using to see if we can optimize it.
I'm suspicious of the left join. See if something like this is faster:
SELECT fv.uuid FROM form_vitals AS fv WHERE fv.uuid IS NOT NULL AND fv.uuid != '' AND fv.uuid != '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' AND fv.uuid NOT IN ( SELECT um.target_uuid FROM uuid_mapping AS um WHERE um.uuid IS NOT NULL AND um.uuid != '' AND um.uuid != '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' AND um.resource = 'Observation' AND um.resource_path = 'category=vital-signs&code=3150-0' ) LIMIT 1000;
Or to really dummy-proof it for MySQL:
SELECT fv.uuid FROM form_vitals AS fv WHERE fv.uuid IS NOT NULL AND fv.uuid != '' AND fv.uuid != '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' AND fv.uuid NOT IN ( SELECT um.target_uuid FROM uuid_mapping AS um WHERE um.target_uuid = fv.uuid AND um.uuid IS NOT NULL AND um.uuid != '' AND um.uuid != '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' AND um.resource = 'Observation' AND um.resource_path = 'category=vital-signs&code=3150-0' ) LIMIT 1000;
If that doesn't help, I think it's a matter of too much data. :)
@sunsetsystems That was faster, went from 0.250 to 0.170 seconds on the mysql console. I'll plug it in, I'm still thinking we may have to rework how we populate the uuids, at least for the mapped resources, as I'm guessing we will still cumulatively hit 5-7 seconds per api request which is pretty slow.
I'm not familiar with that code but it sure sounds like you're right.
- Routing key handling - PHP RabbitMqBundle
- Docker setup pgpass file - OpenSlides
- ipsec协商失败,请大佬解惑 - Shell setup-ipsec-vpn
- Reload the left view table - Swift LGSideMenuController
- transmission A use-after-free bug in concurrent environment C++
- unable connect to other instances - Ruby mastodon
- SelectOneMenu: regression due to today´s changes? - Java primefaces