This the second half of my efficiency question but I kept it separate because this is specifically regarding finding "speed" in an instance reference.
I have this logic as part of a caselist filter:
((#user/current_level2_code = ((instance('casedb')/casedb/case[@case_id = current()/index/parent]/level2))))
and
(
((instance('casedb')/casedb/case[@case_type = 'household_member'][@status='open'][index/parent = current()/index/parent][resident_id = current()/resident_underfive_motherid]/location = '1')
and (location='1') )
)
How can I best order the instance references to get a "quick" response from this caselist?
For reference, on a mobile device with:
-about 2,500 cases with about 1,500 that satisfy condition one (the level2 question)
- of those 1,500 approx 1,000 are household_members
This filter (to include the age-bracketing formulas in my previous question) takes approximately 120 seconds to execute, resulting in a list of 9 cases.
Best Answer
This is a particularly tricky answer because it depends on a number of factors.
In your case you are doing a lot of index referencing, so the most important thing to do is ensure that you are running at least CommCare 2.34.0, a lot of query planning optimizations were introduced in 2.33 and 2.34 and reduce a lot of exponential or polynomial lookups to linear time.
It looks like all of your references follow best practices. Do you know whether the device facing the 120 second lookup is up to date on the mobile version of CommCare?