Web Wiz Forums - Opening Topic = High CPU usage

Opening Topic = High CPU usage

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=24614
Printed Date: 06 April 2026 at 12:12pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Opening Topic = High CPU usage

Posted By: moti
Subject: Opening Topic = High CPU usage
Date Posted: 12 October 2007 at 10:23am

I have a topic about 100 pages , whenever a user opens this topic CPU usage of SQL goes to %45 and some times more than %60 , I don't know this is normal or no,

one of my friends told me this is not normal and its because of bad writing of query,

Average cpu usage of my forum is about %25 , is that normal ?

I have checked with Profiler and it shows me this line of query :


'Intilise SQL query to get all the posts
'Use a LEFT JOIN for the Guest name as there may not be a Guest name and so we want to include null values
strSQL = "" & _
"SELECT " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message, " & strDbTable & "Thread.Message_date, " & strDbTable & "Thread.Show_signature, " & strDbTable & "Thread.IP_addr, " & strDbTable & "Thread.Hide, " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.Username, " & strDbTable & "Author.Homepage, " & strDbTable & "Author.Location, " & strDbTable & "Author.No_of_posts, " & strDbTable & "Author.Join_date, " & strDbTable & "Author.Signature, " & strDbTable & "Author.Active, " & strDbTable & "Author.Avatar, " & strDbTable & "Author.Avatar_title, " & strDbTable & "Group.Name, " & strDbTable & "Group.Stars, " & strDbTable & "Group.Custom_stars, " & strDbTable & "GuestName.Name" & _
"FROM (" & strDbTable & "Group INNER JOIN (" & strDbTable & "Author INNER JOIN " & strDbTable & "Thread ON " & strDbTable & "Author.Author_ID = " & strDbTable & "Thread.Author_ID) ON " & strDbTable & "Group.Group_ID = " & strDbTable & "Author.Group_ID) LEFT JOIN " & strDbTable & "GuestName ON " & strDbTable & "Thread.Thread_ID = " & strDbTable & "GuestName.Thread_ID " & _
"WHERE " & strDbTable & "Thread.Topic_ID = " & lngTopicID & " "

this is from forum_post.asp file

it take long time to run

Replies:

Posted By: WebWiz-Bruce
Date Posted: 12 October 2007 at 11:33am

100 Pages is allot for 1 topic, you maybe better off having a shut off point to start again.

However, it shouldn't use that much processing power. We have a database server serving over 100 Web Wiz Forums, with some with on average over 100 active users and forums with over 400,000 posts. Yet the CPU usage rarely goes above 10% with the average being under 5%.

SQL Server does use ALLOT of resources and if you are worried about this we would recommend that you use mySQL instead, it uses less than 1% of the resources SQL Server uses, and we find on larger Web Wiz Forums databases that it out performs SQL Server.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: moti
Date Posted: 12 October 2007 at 12:37pm

Thanks Borg

So what do you think about that ? what is the problem that cause much cpu usage ?

why above query uses that much processing power ?

Posted By: WebWiz-Bruce
Date Posted: 12 October 2007 at 1:04pm

In amount of time I think it was somewhere in the region of a week was spent on writing this query and optimising the page as much as possible without repeated hits on the database. However quite a bit of data is required so joins in the query are unavoidable.

Most topics themselves only have a 1 to 20 pages, 100 page topic most have allot of posts within it? Often if topics are getting this long it would be more advisable to start a new forum on that topic rather than sticking all the posts in the one topic.

As most topics only have 1 to 20 pages the page itself has been optimised for this amount of data. If you are wanting topics with larger amounts of posts would need to look at removing data and features displayed with the posts and making them more simplistic so that less data is required from the query.

If you don't allow guests to post in forums the first place you could look is modifying the code to display the posts and removing the join to the tblGuestName table. This would certainly cut down on the required joins for the query.

You may also want to look at the server itself, for example if the server running SQL Server is on a Celeron with 512Mb of memory then you are going to be looking at using allot of processing power, as a Celeron CPU is not really up to the amount of resources required for SQL Server. However, if the server is a P4 or a Xeon Dual or Quad Core, then I would have a look at your settings within SQL server as it should not be using this amount of CPU usage. One area to look is to make sure that memory hungry SQL Server has enough memory, even with simple small databases SQL server want's to have at least 1Gig of memory.

This is where mySQL comes in, in comparison it uses much less memory. We have one server here with 12 database on the SQL server and 100 datbaases on mySQL, with mySQL with the larger databases, yet SQL server is using up almost 4Gig of memory and mySQL is using less than 200Mb.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: moti
Date Posted: 12 October 2007 at 1:46pm

Thank you Borg for taking time and writing this good explanation ,

Yes , I have to close that topic,

No , I don't allow guest to post so I just removed that Join part of query its a little better about 200 millisecend less than old query,

the server is Xeon Dual with 2 GB of ram , server seems ok . I have checked all of the counters within Performance Monitoring everything is ok but CPU usage.

Really I don't know what's the problem,

Posted By: WebWiz-Bruce
Date Posted: 12 October 2007 at 2:10pm

On a Dual Xeon there should be no issue of CPU usage, as I mentioned before we have database severs with over 200 web wiz forums databases running off them and CPU usage never goes over 10%.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: moti
Date Posted: 12 October 2007 at 3:17pm

Borg I got some information from Profiler please take a look and tell is that normal or no

I ran this query from forum_topics.asp page in Query Analyzer


'Read in all the topics for this forum and place them in an array
strSQL = "" & _
"SELECT "
If strDatabaseType = "SQLServer" OR strDatabaseType = "Access" Then
 strSQL = strSQL & " TOP " & intMaxResults & " "
End If
strSQL = strSQL & _
" " & strDbTable & "Topic.Topic_ID, " & strDbTable & "Topic.Poll_ID, " & strDbTable & "Topic.Moved_ID, " & strDbTable & "Topic.Subject, " & strDbTable & "Topic.Icon, " & strDbTable & "Topic.Start_Thread_ID, " & strDbTable & "Topic.Last_Thread_ID, " & strDbTable & "Topic.No_of_replies, " & strDbTable & "Topic.No_of_views, " & strDbTable & "Topic.Locked, " & strDbTable & "Topic.Priority, " & strDbTable & "Topic.Hide, " & strDbTable & "Thread.Message_date, " & strDbTable & "Thread.Message, " & strDbTable & "Thread.Author_ID, " & strDbTable & "Author.Username, LastThread.Message_date, LastThread.Author_ID, LastAuthor.Username, " & strDbTable & "Topic.Event_date " & _
"FROM " & strDbTable & "Topic" & strDBNoLock & ", " & strDbTable & "Thread" & strDBNoLock & ", " & strDbTable & "Thread AS LastThread" & strDBNoLock & ", " & strDbTable & "Author" & strDBNoLock & ", " & strDbTable & "Author AS LastAuthor" & strDBNoLock & " " & _
"WHERE ("
'Do the table joins
strSQL = strSQL & " " & strDbTable & "Thread.Author_ID = " & strDbTable & "Author.Author_ID " & _
 "AND LastThread.Author_ID = LastAuthor.Author_ID " & _
 "AND " & strDbTable & "Topic.Start_Thread_ID = " & strDbTable & "Thread.Thread_ID " & _
 "AND " & strDbTable & "Topic.Last_Thread_ID = LastThread.Thread_ID "

and I got these information :

SQL Server parse and compile time:
CPU time = 94 ms, elapsed time = 116 ms.

(386 row(s) affected)

Table 'tblAuthor'. Scan count 2, logical reads 292, physical reads 0, read-ahead reads 0.
Table 'tblTopic'. Scan count 1, logical reads 341, physical reads 0, read-ahead reads 0.
Table 'tblThread'. Scan count 2, logical reads 2406, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 4804 ms.

As you see its only 386 rows , but look at CPU time and elapsed time its abut 5 sec

by the way , I saw this query in Profiler which is one of the queries which take time to run (As I see in profiler) , I have written this for getting number of users posts

SELECT Count(tblThread.Thread_ID) AS Posts FROM tblThread, tblTopic WHERE tblThread.topic_id = tblTopic.topic_id AND (tblTopic.forum_id = 1 OR tblTopic.forum_id = 3 OR tblTopic.forum_id = 9) AND tblThread.Author_ID = '125';

Does it have any problem ? is the query ok ? can I write it in better way ?

Another question , you told me that your server doesn't go above %10 , you mean average, is that correct ? because some times server needs more processing on some queries .