What are the Best Practices For SQL Inserts on Large Scale in reference to ad impressions?

Question

I am working on a site where I will need to be able to track ad impressions. My environment is ASP.Net with IIS using a SQL Server DMBS and potentially Memcached so that there are not as many trips to the database. I must also think about scalability as I am hoping that this application becoming a global phenom (keeping my fingers crossed and working my ass off)! So here is the situation:

• My Customers will pay X amount for Y Ad impressions
• These ad impressions (right now, only text ads) will then be shown on a specific page.
• The page is served from Memcached, lessening the trips to the DB
• When the ad is shown, there needs to be a "+1" tick added to the impression count for the database

So the dilemma is this: I need to be able to add that "+1" tick mark to each ad impression counter BUT I cannot run that SQL statement every time that ad is loaded. I need to somehow store that "+1" impression count in the session (or elsewhere) and then run a batch every X minutes, hours, or day.

Please keep in mind that scalability is a huge factor here. Any advice that you all have would be greatly appreciated.

Answer 1

Something you can do is increment the counts into a less permanent store, and periodically (every 1 minute, 5 minutes, hour..) sync into your more reliable database.

If your temporary store goes down, you lose the hit counts for some short period of time; the net effect of this is that in a rare event of a malfunction, the people paying for ads get some free impressions.

You can send a "atomically increment this value by +1" command to memcache. You could also do something like write a line to a flat file every time an ad is displayed, and have your "every 5 minute" sync job rotate the log, then go through counting all the lines in the file that was just rotated out.

Answer 2

I've seen projects deal with this by deploying SQL Server Express edition on each web farm server and relying on Service Broker to deliver the track audit to the central server. Because each server in the farm updates a local SQL instance, they can scale as high as the sky. Service Broker assures the near-real-time reliable delivery. I've seen web farms handle 300-400 requests per second on average, 24x7 over long time, and with the queued nature of Service Broker being able to absorb spikes of 5000-7500 hits per second for hours at end, and recover in reasonable time, without audit loss and with the backlog staying under control.

If you really expect to scale and become the next MySpace, then you should learn from how they do it, and queue based asynchronous, decoupled processing is the name of the game.