Optimizing query to Obs table with very high cardinality

Development
1

How does one go about optimizing the following query?

select e.encounter_id from (select patient_id, max(encounter_id) as encounter_id from encounter group by patient_id) e inner join obs o1 on o1.encounter_id=e.encounter_id inner join obs o2 on o2.encounter_id=e.encounter_id where o1.concept_id=1109 and o1.value_coded=1107 and o2.concept_id=1261 and o2.value_coded=1257

Any thoughts will be very helpful.

2

What’s the purpose of the query in plain english? Don’t you need to return any attribute from obs ?

Solution without group by:

select e.encounter_id
from encounter e
INNER JOIN obs o1 ON o1.encounter_id=e.encounter_id
INNER JOIN obs o2 ON o2.encounter_id =e.encounter_id
WHERE o1.concept_id=1109
AND o1.value_coded =1107
AND o2.concept_id  =1261
AND o2.value_coded =1257
AND not exists (select 1 from encounter other where other.patient_id = e.patient_id and other.encounter_id > e.encounter_id);
3

Will something like this work:

select distinct e.encounter_id from (select patient_id, max(encounter_id) as encounter_id from encounter e inner join obs o1 on o1.encounter_id=e.encounter_id where concept_id=1109 and value_coded=1107 group by patient_id) e inner join obs o2 on o2.encounter_id=e.encounter_id  where o2.concept_id=1261 and o2.value_coded=1257
4

Below is the description of what the concepts are.

1109 = PATIENT REPORTED CURRENT PCP PROPHYLAXIS
1107 = NONE
1261 = PCP PROPHYLAXIS PLAN
1257 = CONTINUE REGIMEN

So basically the query says get me the latest encounters (from which you can get the patient) which have PCP prophylaxis plan as continue regimen but with no reported current pcp prophylaxis

5

Thanks @lluismf , this has cut down the time by more than 50%!

@tmdugan this query seems like it is doing the right thing, however when I try it, I get more results than the original query!

1 Like