Optimizing a .NET + Angular project that fetches millions of records from the database [closed]

Question:
You are tasked with optimizing a .NET + Angular project that fetches millions of records from the database and sends them to the client application for filtering. Additionally, when the data is updated, the backend updates all records with their statuses. The statuses are fetched at runtime from a separate table and incorporated into a list of models containing the status and relevant data.

In the current implementation:

• A query returns a list of records, and then the IDs of those records are used in another query to fetch additional details.
• Once this query returns, it is sent to yet another query to retrieve the statuses related to these records.
• Additionally, the records contain references to files, which are retrieved using yet another query.

Questions:

1. 
   How would you refactor the current query structure to:
   
   • Eliminate the repetition issue caused by joins?
   • Efficiently handle multiple statuses or associated files for a single record?
2. 
   What alternative strategies could you implement to reduce the number of queries while ensuring the data remains accurate and complete?
   
   
3. 
   How would you handle scenarios where a record has multiple statuses and/or files, and aggregate this data into a non-redundant format for the client?
   
   
4. 
   Would you propose a caching mechanism to improve performance and explain how you would implement it in this scenario.
   
   
5. 
   How would you minimize unnecessary data transfer between the server and client while enabling efficient client-side filtering?
   
   
6. 
   If real-time updates to record statuses or associated files are required, how would you design the solution to accommodate this requirement?
   

Self-Tested Solution:
We attempted to optimize this process by adding joins across these queries to retrieve all necessary information in a single operation. However, this led to record repetition, as records with multiple statuses or associated files were duplicated in the result set.

Continue reading...