Data driven design problem - data counting

Question

I want to provide about 10 different 'statistics' to my users. Each stat represents the count returned by a different query of my database. Each stat/query is specific to the current user. What is going to be the most efficient way to achieve this? Am I right to think that running the stat queries, for each user on each page of my site (to provide them with a realtime picture) is not the best way of doing it?

Advice appreciated.

Edit:

I am working on the basis of between 100 - 1000 concurrent users.

Answer 1

Could you not store the values in cache for a few minutes? Especially if the calculations are heavy this would help performance while keeping the statistics almoast real-time.

But it depends on how many users you have and how expensive the calculations are.

Answer 2

It all depends on how much traffic and queries your server can handle, as well as how often those numbers change and how up-to-date they have to be to the users.

ASP.NET offers the ability to set specific cacheability to controls, and make the cache vary by userid or any other parameter you want, you could take advantage of that.

Answer 3

My first thought is that it's one of those things that it's hard to get perfect the first time so try and make sure your design makes it possible to change the way you do things. E.g. have a single GetUserStats function that returns a UserStats object so that there is only one place in your site where this logic is implemented. That should make it easier to change things later.

You're right that you don't want multiple queries per page. I'm not much of a data guy but I would think that a single stored procedure that returns all the stats for a user in one go would be a good start. This would then allow changes to be made to how the stats are calculated/updated (query, batch, triggers,...) without any changes having to be made to your code.

Answer 4

I'd be tempted to keep a 'running total' of the statistics as I go and store in the database, it's a simple query to retrieve the data when required. Depending on how critical it is that the values displayed to the user are up to date some kind of caching may be required.

We have a number of apps at work where data stats are of critical importance to the user. Implementing a system where the stats are stored in the db using the 'running total' method has yielded significant performance improvements. Having said that our volume of concurrent users is much lower than yours.