An effective way of calculating total sum of all transaction amounts for an account

Question

I have a system that lets users entering financial transactions including dollar amounts. Once transaction is added\updated or removed from the system the account total has to be re-calculated and displayed to the user instantly. Users have access to certain accounts in the system but not all.

Below is a link to the screenshot of the tables that hold account and transaction data.

https://i.stack.imgur.com/c1Gef.png

Currently in order to get the account total I use the following query:

select sum(t.Amount) 
from [Transaction] t
join [Account] a on a.AccountId=t.AccountId
where a.AccountId=@AccountId

I have non-unique non-clustered index on the Transaction table on AccountId and Amount columns which allows the query to quickly find transactions by AccountId.

It does what I want but my concern is that Transaction table is growing pretty fast, currently I have around 1 million records there and over time I expect it to reach tens of millions. Also the number of users that can actively edit transactions is growing as well, I have around 5500 of them in the system.

I am looking into a solution to limit the use of Transaction table when getting the total $ for the account so the system can scale out. I have 2 solutions for that:

1. Calculate account total on demand by asking users to click on a certain button when they need that information. I might go that route but I want to still explore my options with the real time update of the account total.
2. Store a running total some place else and use it to calculate account total. There is a limitation that is associated with it as everything has to be done through a single interface that would know about all these nuances. Editing transactions in the database would be tricky since you have to make sure the running total is updated as well.

I am looking for other alternatives to calculating account total in real time without worrying about database performance.

Answer 1

My suggestion would be to use the last solution only if you have performance issues.
Handling a lot of data is the job of a database, so if your data are correctly structured, a SQL query can perform well even with thousands of rows.
So, first try keep going like that. If you encounter performance issues, then try to find a solution.

If you decide to use the solution of keeping a calculated Total field, to work around the risk of updating the data without updating to Total field, you should use triggers : each time a row is added, removed or updated, you should add or substract the Total value.

Here's some help on how to create triggers.

Answer 2

Even if the table is large the index on accountId would cut the number of rows involved in the query to a relatively small number of rows in an efficient manner.

If you have a lot of queries of this type and can live with a summaries being less fresh than the actual transactions, you can considering setting a separate database for reporting where you can denormalize, build summaries etc. You can then create jobs to periodically update these tables (that's the basic idea behind "data warehouse" - you probably don't need the full-blown version but the same ideas apply).