Fixing Forum Structure Help

[NeoTurtle]

Hello everyone. I'm using SQL 7.0 on NT4. I made a forum that uses SELECT COUNT(*) to count the number of replies and display them. Unfortunately, I think that's slowing down the system. I made a column called "Replies" and now can't seem to get a script to update the Replies column. The forum works like this:
Every post has a unique Post ID and a ReTo column. ReTo is 0 if it's a new thread ot it has the Post ID of which it is replying to. I tried to do something in ASP to loop through each Post but took too long and kept timing out. The script works great for 1 to 500 posts at a time. But I need to do about 20,000 posts! Here's the script for updating 1 post, maybe someone can help me make it work for all rows:

UPDATE tblRMEWebBoard
SET Replies =
(SELECT COUNT(*) FROM tblRMEWebBoard WHERE ReTo = 1)
WHERE PostID = 1

That will count the number of replies to post 1 and set the Replies column to however many replies there are. I tried using WHERE IN's and other statements without much success. Hope someone can help!!!

Thanks a lot in advance!!
Turtle

 

[tlbroadbent]

The update query you posted should be very qick if

1) the table is properly indexed and
2) the update is not being blocked by other activity on the table.

Indexing can affect locking/blocking on SQL Server so start with the indexes. For good performance you must have an index on PostID and ReTo. I recommend a clustered index on ReTo and a nonclusted, unique index on PostId. A primary key on PostId would work well. Avoid indexes the other columns of the table unless really needed. Too many indexes can slow updates and increase blocking because SQL Server must acquire locks to perform index page updates.

If the indexes are already in place then I would monitor SQL Activity for locking and blocking conditions. Try to determine what is causing locking/blocking conditions.

See the following articles.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q251004

http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_00mr.asp

http://www.ntssug.com/performance.ppt

You can avoid some locking prolems by using the NOLOCK hint on SELECT queries.

Select * from tblRMEWebBoard With (NOLOCK)

See

http://www.sql-server-performance.com/lock_contention_tamed_article.asp.

Check the following for SQL Server performance related links and articles.

http://tlbroadbent.home.attbi.com/sql_and_access/sql_performance.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.