Tracking user actions for reward/achievement system

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.

I am trying to determine the most efficient way to implement this, and I'm a little stuck. My current solution is:

1. 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.
2. 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).
3. 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.

Steps 2 and 3 are where my concerns lie as I would be performing those queries every single time the user made a comment.

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):

achievements = From ach In searchCtx.Achievements
               Where ach.CategoryID = achievementCategoryID And ach.IsActive = 1
               Select ach 

For Each achmt As Achievement In achievements
    Dim achieve开发者_Python百科d 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

Next

The SqlToExecute is calling a function that returns a boolean, something like:

select count(id) from comment where userid = @userID

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.

Any suggestions would be much appreciated!!

It depends on how many users actions you have. If you have few (say under 10k per day) you have a fine way, leave it be.

If it is larger you could change the strategy into either do the action only every once in a while, so instead of calling this function for all users every time they do an action, call it for all users that were active in the last 30 minutes at once. Be sure to check that the question only runs one time at any given time, if one check takes longer than 30 minutes the next one may not start!

Or store the information in a meta table (with the cols userid, number of comments, etc) and manipulate that table relative to the user action (like increase number of comments) and call the check after every action.

For a more specific answer, please provide data such as how many users you have, of which: how many are active? How many actions are there per day etc. The question on the best performing way is highly dependent on the statistics of the use case.