In a .NET Core web app when handling large amounts of data calculated from multiple tables in order to calculate lots of consistent metrics in the form of reports, I’m looking to learn more about design choices that can be made to improve the overall performance of calculating these values. I understand this might be a bit vague but please bear with me.

Essentially users can enter large amounts of data and the data is stored in SQL Sserver in hundreds of different tables (standard). Most of the statistics are calculated via stored procedures (when complicated enough) or linq queries.

The issue is essentially when generating reports it requires a lot of calculations to arrive at a metric e.g. a value to represent a users performance in activities across a week, it needs to run complicated stored procedures to collect data from multiple tables e.g. user, team, activities, activtylogs etc. and this performance metric may be reused in lots of different places in various forms e.g daily, weekly, for a given team, for a given user, etc.

However these calculations end up taking a decent amount of time (usually seconds where complicated enough). Assuming there is minimal improvements that can be made to the stored procedures themselves, what are some ways to essentially aggregate and process data to make metrics easily available / transformable and reduce overall time it takes to generate these metrics / reports.

Some solutions I’m aware of:

• The database has already been separated into a reporting database that updates values at set interval
  • the issue with this is that most of the metrics required on various pages need to reflect live data from the transactional db
  • I’m struggling to find any relevant material for adaptations that can be made to allow for live data but have read brief mentions of messaging (?)
• Having a large class that may preemptively collect and keep relevant calculations, though this seems questionable as a design choice?
  • this would be able to help reduce the number of times the same calculations are made but would require a decent amount of processing / memory, and when keeping this alive throughout the app does not seem efficient(?) when there is such a large range of metrics that may or may not be used in different pages / reports and there would need to be some sort mechanism to update when data is changed from other areas.

If utilisating and expanding the reporting database is the only viable method, any materials where I could look into options for adapting this to live data would be appreciated.