title: Tracking user actions for reward/achievement system
body: <p>I am in the process of implementing a user awards/achievement system for a web site I've been working on for the past year.  The basic premise is similar to what you would find on GameTrailers, or GiantBomb.com: The user gets award badges (a visual representation of the achievement) for doing things like: Making [x] many comments, or adding [x] many items to their wishlist, etc.</p>
  
  <p>I am trying to determine the most efficient way to implement this, and I'm a little stuck.  My current solution is:   </p>
  
  <ol>
  <li>Create each achievement in the database (this will happen regardless).  The achievement has a category, an acceptance number, and SQL to execute dynamically in order to determine if the acceptance has been met.</li>
  <li>Whenever a user performs an action that could potentially yield an achievement (such as making a comment on an article), I run a SQL statement to determine which achievements they're eligible for (I filter based on the category, and remove ones they've already completed).</li>
  <li>From the achievements returned in that query, I go through each and execute the dynamic SQL to determine if the acceptance number as been met.  If so, the user completed the achievement.</li>
  </ol>
  
  <p>Steps 2 and 3 are where my concerns lie as I would be performing those queries every single time the user made a comment.</p>
  
  <p>An example of the above scenario is below (this code isn't entire perfect, just a mock.. btw, userid is something I retrieve, not something the user enters):</p>
  
  <pre><code>achievements = From ach In searchCtx.Achievements
                 Where ach.CategoryID = achievementCategoryID And ach.IsActive = 1
                 Select ach 
  
  For Each achmt As Achievement In achievements
      Dim achieved As Boolean
      Dim sqlToExecute As String = qst.SQLToRun
      sqlToExecute = sqlToExecute.Replace("@USERID", "'" + userid.ToString + "'")
      sqlToExecute = sqlToExecute.Replace("@TARGETVAL", achmt.AcceptanceNumber)
      achieved = searchCtx.ExecuteQuery(Of Boolean)(sqlToExecute).First
  
      If achieved Then
          ' Add Conquest to User Achievemnets
          Dim usrAhmt As New UserAchievement
          usrAhmt .UserID = userid
          usrAhmt .DateCompleted = DateTime.Now
          usrAhmt .AchievementID = achmt .ID
          searchCtx.UserAchievements.InsertOnSubmit(usrAhmt)
      End If
  </code></pre>
  
  <p>Next</p>
  
  <p>The SqlToExecute is calling a function that returns a boolean, something like:</p>
  
  <pre><code>select count(id) from comment where userid = @userID
  </code></pre>
  
  <p>So having said all of that, I think this will work, but I'm concerned about performance.  I'm not too familiar with web programming, but would it be better to perhaps keep a UserStats object in the session, and then operate on that to determine if the user has made enough comments to complete an achievement?  This would be less dynamic, but possibly less stressful on the SQL server.</p>
  
  <p>Any suggestions would be much appreciated!!</p>