Working with split lookup tables


#1

Hi, I have a lookup table that has now exceeded 3850 rows since we expanded our coverage from one country to several. I think the sheer size of this table may be related to a degradation in responsiveness on our mobile devices in and around form questions that reference this rather large lookup table.

I’ve looked into the help pages about splitting large lookup tables into two. I think I understand how to mechanically split a table, but those help pages are sketchy at best in explaining how to dynamically reference the correct table out of two or more “split” tables.

Maybe some specifics from my situation might help illustrate the challenge. My large original table named “district” contains all of the districts in each state in India, as well as all of the counties/parishes in each state in the United States. It seems obvious to split the table into two, one for the districts in India (perhaps call that split table “districts_india”), and the other for counties/parishes in the U.S. (perhaps call that split table “districts_us”) What’s not obvious to me is how I would write an instance statement that would direct a lookup table question on a form to query the “districts_india” split table table vs. the “districts_us” split table.

One strategy I’ve thought of is to leverage the national_id (or its associated name) to which each user is assigned in my organizational hierarchy. So, if the mobile user is assigned to India, then the form question would query the “district_india” split table, and if the mobile user is assigned to the U.S. then the lookup table question would query the “district_us” split table. Seems logical, but perhaps easier said than done. How in the world can I work national_id, or something like it, into an instance statement as a variable that would dynamically determine which split table would be queried? Keep in mind that I need this method to be scalable beyond just these two countries. I can’t be rewriting code just because our organization entered another country.

Any wisdom on this from anyone out there in the “CommCare nation”?

Thanks in advance,

Kevin


(Jessica Long) #2

Hi Kevin,

Great question, and I’m really glad you’re thinking about this.

Look-ups in large look-up tables can definitely degrade CommCare performance. I would recommend doing some testing with different sizes of look-up tables to better understand the effect this has on your particular app and device.

Your strategy of breaking up the look-up tables into multiple smaller tables is a kind of partitioning, and it’s a recommended best practice for creating maintainable, scalable apps. Please see this wiki page for additional details about how to configure the filtering as you describe above: https://confluence.dimagi.com/display/commcarepublic/Setup+a+Question+with+Filtered+Choices

My recommendation would probably be to add a Custom User Field called “country” or “country_code” for each mobile user. Then you can compare the value of the mobile user’s country to a field inside your loo-up table. You may want to drill down to something more granular than country (perhaps a country / state combination)?

Eventually, you may also want to consider indexing the look-up table fields that you’re using for the filtering (i.e. “country”). I will try to find documentation similar to what’s above to link you to more information about implementation.