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
Asked Oct 16 '21 12:10
avatar adunsulag
adunsulag

9 Answer:

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.

1
Answered Sep 30 '21 at 20:56
avatar  of sunsetsystems
sunsetsystems

@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.

1
Answered Sep 30 '21 at 21:37
avatar  of adunsulag
adunsulag

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`);
1
Answered Sep 30 '21 at 21:38
avatar  of adunsulag
adunsulag

Thanks for the clarification. An EXPLAIN might help for clues about optimizing.

1
Answered Sep 30 '21 at 21:59
avatar  of sunsetsystems
sunsetsystems

@sunsetsystems Yea, I've been playing with it, EXPLAIN is what I've been using to see if we can optimize it.

1
Answered Sep 30 '21 at 22:10
avatar  of adunsulag
adunsulag

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;
1
Answered Sep 30 '21 at 22:52
avatar  of sunsetsystems
sunsetsystems

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. :)

1
Answered Sep 30 '21 at 23:07
avatar  of sunsetsystems
sunsetsystems

@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.

1
Answered Sep 30 '21 at 23:22
avatar  of adunsulag
adunsulag

I'm not familiar with that code but it sure sounds like you're right.

1
Answered Sep 30 '21 at 23:27
avatar  of sunsetsystems
sunsetsystems