Efficiently record and store page view counts in the database?

Question

Sites like StackOverflow count, save, and display the view counts for pages. How do you do it efficiently? Let's take view counts for StackOverflow questions as the example. I see the following options.

Option 1. When the app receives a request for a question, increment the count in the question table. This is very inefficient! The vast majority of queries are read-only, but you're tacking on an update to each one.

Option 2. Maintain some kind of cache that maps new view counts to questionIds. When the app receives a question request, increment the cached view count for the question id. You’re caching the marginal increase in views. So far, so good. Now you need to periodically flush the counts in the cache. That's the second part of the problem. You could use a second thread or some kind of scheduling component. This really is a separate question and partly depends on your server platform (I'm using Java). Or, rather than using a separate thread, after a certain number of counts stored in the cache, you could do an update within the request thread that reached the threshold. The update functionality could be incapsulated in the cache giving the cache some IQ points.

I like the idea of a cache that gets flushed when a threshold is reached. I'm curious to know what others have done and if there is a better way.

Answer 1

I agree that writing each pageload to the database is inefficient. My approach is to cache the requests then commit them once an hour. Each time I update the cache I compare the current time to LastWriteTime, and if more than an hour has passed, I write. It's an ASP.NET app, so I have a final commit in the Application's shutdown method. The latter is not guaranteed to run, however is considered an acceptable loss.