How to log changes to database every 5 minutes in a high-transaction application with C# and SQL?

Question

Imagine this scenario: you have a WCF web service that gets hit up to a million times a day. Each hit contains an "Account ID" identifier. The WCF service is hosted in a distributed ASP.NET cluster and you don't have Remote Desktop access to the server.

Your goal is to save "number of hits per hour" for each Account ID into a SQL database. The results should look like this:

[Time], [AccountID], [NumberOfHits]
1 PM, Account ID (Bob), 10 hits
2 PM, Account ID (Bob), 10 hits
1 PM, Account ID (Jane), 5 hits

The question is: How can you do this without connecting to a SQL server database on every hit?

Here's one solution I thought of: Store the temporary results in a System.Web.Cache object, listen to its expiration, and on Cache Expiration, write all the accumulated data to the database when Cache expires.

Any thoughts on a better approach?

Answer 1

Deffered update is the key, indeed, and you are on the right path with your local cache approach. As long as you don't have a requirement to display the last-update-count on each visit, the solution is simple: update a local cache of account_id->count and periodically sweep through this cache, replace the count with 0 and add the count to the total in the database. You may loose some visit counts if your ASP.Net process is lost, and your display hit count is not accurate (Node 1 int he ASP farm returns it's lats count, Node 2 returns its own local one, different from Node 1).

If you must have accurate display of counts on each return result (whether this is an page return or a service return, matter little) then it gets hairy quite fast. Centralized cache like Memcache can help to create a solution, but is not trivial.

Here is how I would keep the local cache:

class HitCountCache
{
   class Counter 
   {
       public unsigned int count {get;set}
       public accountid {get;set}
   };

   private Dictionary<accountType, Counter> _counts = new Dictionary<...>();
   private Object _lock= new Object();

   // invoke this on every call
   //
   void IncrementAccountId (accountId)
   {
      Counter count;
      lock(_lock) 
      {
         if (_counts.TryGetValue (accountId, out count))
         {
            ++count.count;
         }
         else
         {
            _counts.Add (accountId, 
                new Counter {accountId = accountId; count=0});
         }
      }
   } 

   // Schedule this to be invoked every X minutes
   //
   void Save (SqlConnection conn)
   {
      Counter[]  counts;

      // Snap the counts, under lock
      //
      lock(_lock)
      {
          counts = _counts.ToArray();
          _counts.Clear();
      }

      // Lock is released, can do DB work
      //
      foreach(Counter c in counts)
      {
          SqlCommand cmd = new SqlCommand(
                 @"Update table set count+=@count where accountId=@accountId", 
                 conn);
          cmd.Parameters.AddWithValue("@count", c.count);
          cmd.Parameters.AddWithValue("@accountId", accountId);
          cmd.ExecuteNoQuery();
      }
   } 
}

This is a skeleton, it can be improved, and can also be made to return the current total count if needed, at least the total count as known by local node.