Web Wiz Support and Community Forums : Opening Topic = High CPU usage https://forums.webwiz.net/ Copyright (c) 2006-2013 Web Wiz Forums - All Rights Reserved. Mon, 06 Apr 2026 12:14:53 +0000 Fri, 12 Oct 2007 15:17:19 +0000 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 12.08 360 https://forums.webwiz.net/RSS_post_feed.asp?TID=24614 <![CDATA[Web Wiz Support and Community Forums]]> https://forums.webwiz.net/forum_images/web_wiz_forums.png https://forums.webwiz.net/ <![CDATA[Opening Topic = High CPU usage : Borg I got some information from...]]> https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128499.html#128499 Author: moti
Subject: 24614
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 .
 
 
 
 


Edited by moti - 12 October 2007 at 11:30pm]]> Fri, 12 Oct 2007 15:17:19 +0000 https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128499.html#128499 <![CDATA[Opening Topic = High CPU usage : On a Dual Xeon there should...]]> https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128495.html#128495 Author: WebWiz-Bruce
Subject: 24614
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%.


Edited by -boRg- - 12 October 2007 at 2:10pm]]> Fri, 12 Oct 2007 14:10:20 +0000 https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128495.html#128495 <![CDATA[Opening Topic = High CPU usage : Thank you Borg for taking time...]]> https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128494.html#128494 Author: moti
Subject: 24614
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,
Unhappy
]]> Fri, 12 Oct 2007 13:46:32 +0000 https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128494.html#128494 <![CDATA[Opening Topic = High CPU usage : In amount of time I think it...]]> https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128487.html#128487 Author: WebWiz-Bruce
Subject: 24614
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.




Edited by -boRg- - 12 October 2007 at 1:10pm]]> Fri, 12 Oct 2007 13:04:14 +0000 https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128487.html#128487 <![CDATA[Opening Topic = High CPU usage : Thanks Borg So what do you think...]]> https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128483.html#128483 Author: moti
Subject: 24614
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 ?
 
 
]]> Fri, 12 Oct 2007 12:37:06 +0000 https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128483.html#128483 <![CDATA[Opening Topic = High CPU usage : 100 Pages is allot for 1 topic,...]]> https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128481.html#128481 Author: WebWiz-Bruce
Subject: 24614
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.


Edited by -boRg- - 12 October 2007 at 11:35am]]> Fri, 12 Oct 2007 11:33:58 +0000 https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128481.html#128481 <![CDATA[Opening Topic = High CPU usage : I have a topic about 100 pages...]]> https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128480.html#128480 Author: moti
Subject: 24614
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
 
 


Edited by moti - 12 October 2007 at 10:32am]]> Fri, 12 Oct 2007 10:23:50 +0000 https://forums.webwiz.net/opening-topic-high-cpu-usage_topic24614_post128480.html#128480