Searching for locations is slow

[mahalakshme]

Issues:

Loading of locations takes more time in all the places. By the time the results display the user loses patience and moves away from the page.
Env: prod, org name: Goonj

1. In Distribution registration form in DEA,

https://{domain_name}/locations/search/find?title=a&typeId=1396&size=100&page=0 - taking 23 secs in DEA

2. In Admin tab, on clicking a test catchment the already set locations to display takes a minute

https://{domain_name}/locations/search/findAllById?ids={someLocationId} - took 37 secs - fired to fetch the location name already added

https://{domain_name}/locations/search/find?title=&page=0&size=1000&sort=id%2CDESC - took 1.6 min - fired to show a list of locations

https://{domain_name}/locations/search/find?title=ab&page=0&size=1000&sort=id%2CDESC - took 1.9 min - fired as I typed a location name to show the appropriate search results - to add the location starting with ab to the catchment

AC:

The location search and save should be smooth. So the above API calls should not take more than a second or two.

Root cause:

Joining with title_lineage_locations_view

Technical solution - suggestions:

• Why cant we have the title_lineage in address_level table itself - none of the advantages that view provides seem to be relevant for us w.r to title_lineage_locations_view - one additional thing need to do is, whenever lineage(ids) are updated, this also needs update. But considering updating hierarchy is not as frequent as location search, priority for efficiency of location search seems to be important.
  • If this is done, need to fix all the queries where we do join with title_lineage_locations_view
  • The in-memory search done on title_lineage_locations_view to optimise individual search via DEA might not be needed.
• for DEA registration form this might help: In hierarchical location setting search - When parent_id is passed need not join with title_lineage_locations_view since the user will know the parent - applicable for DEA search ie. need not show the dropdown list with hierarchies.

Reason for priority:

• Currently API search of individual is very slow - one reason is join with title_lineage_locations_view. Fixing the above, will help in fixing everywhere in a uniform way wherever we do join with title_lineage_locations_view.

Old: Ignore:

Below might help:

• For adding location to catchment, and in DEA search this might help: From the plan(explain analyse result) realised that, left join with title_lineage_locations_view in locationProjectionBaseQuery is the bottleneck. Hence, adding an index on lowest_point_id column might help.

Old

• adding an index to name column in address_level table. Also joint index of typeId and parentId columns.
• debouncing the API call in type-ahead functionality - doesn't seem to be done in Admin app search
• Instead of 1000 returning 100 results might be sufficient.
• Inner join instead of left join in locationProjectionBaseQuery to minimise the number of matching rows

Questions:

If joining with the view is the problem, then we can add a separate below UI in the locations page, to be able to mention the hierarchy instead of searching across all the hierarchy. This hierarchical on DEA and Admin can get enabled on when the number of locations exceeds a particular no.

[1t5j0y]

• Continuing with in-memory search approach for title lineages as the computation of title lineages happens for all records when queried via join whereas it is sufficient for us to compute it for a specific page.
• Not introducing column to store title lineages in address_level as they will need to be updated whenever a location name changes in addition to hierarchy changes.

Pending:

• join with title_lineage_locations_view slowing down user subject assignment screen.

QA Notes:

• Web App - DEA
  -- Location form element in both hierarchical and non-hierarchical mode (org config setting toggle 'Show hierarchical location')
• Web App - Admin
  -- Locations page
  -- Locations search
  -- Catchments show
  -- Catchments add locations
• Web App - Tasks
  -- User subject assignment search

[petmongrels]

We should remove - String getTitleLineage() from the interface LocationProjection. It is going to be misleading.
We are not serialising it anywhere.

Also remove null as titleLineage also from the queries.

[himeshr]

Testing notes

Following functionality works as expected.
The performance issue was tested on Staging Env, Goonj Revamp(Gpart) organisation.

• Admin app:
  • Both Location search load and edit are faster
  • Catchment show, Location Load, add / modification are much faster and easy.
• DEA :
  • Search Page, Non-Heirarchial Location search loads and works faster
  • Address selection during Register is much faster
• User subject assignment - Address filter location search is fast (Lahi org)

[mahalakshme]

@1t5j0y does the above fix make DEA search also faster.