ASP.Net + SQL Server Design/Implementation Suggestions

Question

Hello all,

I'm constructing a prototype site for my company, which has several thousand employees and I'm running into a wall regarding the implementation of a specific requirement.

To simplify it down with an example, lets say each user has a bank account with interest. Every 5 minutes or so (can vary) the interest pays out. When the user hits the site, they see a timer counting down to when the interest is supposed to pay out.

The wall I'm running into is that it just feels dirty to have a windows service (or whatever) constantly hitting the database looking for accounts that need to 'pay out' and take action accordingly.

This seems to be the only solution in my head right now, but I'm fairly certain a service running a query to retrieve a sorted result set of accounts that need to be 'paid out' just won't cut it.

Thank you in advance for and ideas and suggestions!

Answer 1

Rather than updating records, just calculate the accrued interest on the fly.

This sort of math is pretty straightforward, the calculations are very likely to be orders of magnitude faster than the continuous updating.

Something like the following:

WITH depositswithperiods AS (SELECT accountid, depositamount, 
     FLOOR(DATEDIFF(n, deposit_timestamp, GETDATE()) / 5) as accrualperiods, interestrate
     FROM deposits)

SELECT accountid, sum(depositamount) as TotalDeposits, 
     sum( POWER(depositamount * (1 + interestrate), accrualperiods) ) as Balance
FROM
   depositswithperiods
GROUP BY accountid

I assumed compounded interest above, and no withdrawals.

The addition of withdrawals would require creating a group of deposits for each time period, taking the sum of those to get a net deposit for each time period, and then calculating the interest on those groups.

Answer 2

I don't know if the interest analogy will hold for your actual use case. If the database doesn't need to be kept up to date for all users at all times, you could apply the AddInterest operation multiple times at once when you need an up-to-date value. That is, whenever the value is displayed, or when the account balance is about to change.

You could do a single nightly update for all accounts.

Answer 3

A good thing to think of when doing this kind of thing is DateTime.

If you are charged 10 pence a minute for a phone call, there isn't a computer sitting there counting every second and working out minutes... It just records the date/time at the start, and the datetime at the end.

As others suggest, just calculate it when the user tries to view it.