I have a monolith application in .net core 3.0 with entity framework core 3.0. using:
- a table with ~3 million records. Its structure is
BusinessUnitId | ProfileId | Amount(it has more fields, but these are important ones);
- another table that looks like this:
ProfileId | Price.
When I need to generate report for all Business Units, I have to read ~3kk records from one table, then match profiles with their price, then generate view model and send it.
Problem is - it takes more than one minute, and this report should appear on web page in form of table, i.e. take not more than ~5 seconds.
I tried to create separate table just for this report, but it's population/recalculation takes like 10 minutes. Currently I think that I will move report data to separate table and make a service that will update it when needed.
What is best practice or standard approach for these kind of problems, when you need to calculate huge amount of data in run time/perform queries that take long to execute, but need it to be fast?