I have a report/issue management application that has an interactive dashboard that allow users to drill from bar, line and area charts to a summary page(data grid). The summary page aggregates the totals for reports represented by the charts. Users are then able to open reports to view the records/issues that are present.The records for the report is displayed in a data grid. Users are then able to change the properties of the records which will intern affect the dashboard charts. For example, a user can open a report and change the state of one of the records from 'open' to 'close'. This is an enterprise application that is used by multiple users at the same time, so changes made to reports will affect multiple users.
I have one central table that manages all the properties of the records/issues for all the reports in the system. I use this table to create a view with other supporting tables to generate the data for the charts and summary pages for the dashboard. I created few(5) covering indexes to improve performance for some of the queries. However, as the data grows in the table, the performance deteriorates. Also, the performance of updates suffer significantly as data grows as well.
I would like to know what can I do to improve the performance of this application given the fact that I am using the same data to generate charts and the data is also changing? Is there a better design that I can explore?
Any help I can get with this problem will be greatly appreciated.