title: An effective way of calculating total sum of all transaction amounts for an account
body: <p>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.</p>
  
  <p>Below is a link to the screenshot of the tables that hold account and transaction data.</p>
  
  <p><a href="http://i.imgur.com/EqV7w.png" rel="nofollow">http://i.imgur.com/EqV7w.png</a></p>
  
  <p>Currently in order to get the account total I use the following query:</p>
  
  <pre><code>select sum(t.Amount) 
  from [Transaction] t
  join [Account] a on a.AccountId=t.AccountId
  where a.AccountId=@AccountId
  </code></pre>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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:</p>
  
  <ol>
  <li>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. </li>
  <li>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.</li>
  </ol>
  
  <p>I am looking for other alternatives to calculating account total in real time without worrying about database performance.</p>