Best approach to cache Counts from SQL tables ?

Posted by pixel3cs on Stack Overflow See other posts from Stack Overflow or by pixel3cs
Published on 2009-07-06T11:21:44Z Indexed on 2010/05/12 17:24 UTC
Read the original article Hit count: 179

Filed under:
|
|

I would like to develop a Forum from scratch, with special needs and customization.

I would like to prepare my forum for intensive usage and wondering how to cache things like User posts count and User replies count.

Having only three tables, tblForum, tblForumTopics, tblForumReplies, what is the best approach of cache the User topics and replies counts ?

Think at a simple scenario: user press a link and open the Replies.aspx?id=x&page=y page, and start reading replies. On the HTTP Request, the server will run an SQL command wich will fetch all replies for that page, also "inner joining with tblForumReplies to find out the number of User replies for each user that replied."

select 
    tblForumReplies.*,
    tblFR.TotalReplies
from 
   tblForumReplies
   inner join 
     (
       select IdRepliedBy, count(*) as TotalReplies
       from tblForumReplies
       group by IdRepliedBy
     ) as tblFR 
     on tblFR.IdRepliedBy = tblForumReplies.IdRepliedBy

Unfortunately this approach is very cpu intensive, and I would like to see your ideas of how to cache things like table Counts.

If counting replies for each user on insert/delete, and store it in a separate field, how to syncronize with manual data changing. Suppose I will manually delete Replies from SQL.

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about count