Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Opening Topic = High CPU usage
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Opening Topic = High CPU usage
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
moti View Drop Down
Groupie
Groupie


Joined: 07 September 2004
Status: Offline
Points: 73 		Post Options Post Options   Thanks (0) Thanks(0)   Quote moti Quote  Post ReplyReply Direct Link To This Post Topic: Opening Topic = High CPU usage
    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
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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
Web Wiz Forums Hosting
ASP.NET Web Hosting
Back to Top
moti View Drop Down
Groupie
Groupie


Joined: 07 September 2004
Status: Offline
Points: 73 		Post Options Post Options   Thanks (0) Thanks(0)   Quote moti Quote  Post ReplyReply Direct Link To This Post 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 ?
 
 
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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
Web Wiz Forums Hosting
ASP.NET Web Hosting
Back to Top
moti View Drop Down
Groupie
Groupie


Joined: 07 September 2004
Status: Offline
Points: 73 		Post Options Post Options   Thanks (0) Thanks(0)   Quote moti Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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
Web Wiz Forums Hosting
ASP.NET Web Hosting
Back to Top
moti View Drop Down
Groupie
Groupie


Joined: 07 September 2004
Status: Offline
Points: 73 		Post Options Post Options   Thanks (0) Thanks(0)   Quote moti Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.