search

pucardotorg / dristi

MIT License
2 stars 12 forks source link

Tech Debt: Optimize DB design #1397

Open subhashini-egov opened 3 weeks ago

subhashini-egov commented 3 weeks ago

Describe the bug Current DB design doesn't include FKs or indexes across all backend services. This reflects in slow search performance across all screens.

To Reproduce Steps to reproduce the behavior:

  1. Go to employee portal and login as judge
  2. Go to home screen
  3. Users see a spinner for a while before cases/hearings etc.. load

Expected behavior Case list for a user should load sub-second.

Additional context UI calls the search APIs to load list of cases/hearings etc.. DB design is not optimized with FKs or Indexes to support fast search.

  1. New DB schema design is uploaded here. Please refer to this and implement.
  2. Ensure the persister configuration is changed.
  3. Ensure search queries are rewritten taking advantage of JOINs and other SQL constructs.
subhashini-egov commented 2 weeks ago

What can be done to optimize search is documented here.

manimaarans commented 2 weeks ago

Hi @subhashini-egov , on this we are taking the following approach

  1. create indexes and test the performance
  2. will work on the one join query with the existing table schema in a new endpoint and test the performance

and also we will alter column datatype and rename in step by step manner if feasible

atulgupta2024 commented 1 week ago

@manimaarans can you point me to which query has been modified so that I can review it once?

@subhashini-egov FYI