Performance tuning, query optimizing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance tuning, query optimizing

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2002-05-02 : 16:04:46

I run a pretty high traffic site (~7 Million page requests a month). Every page on the site makes at least one call to a stored proc and most make many calls. I am noticing that one page in particular causes the CPU to spike up to about 70-90% every time that page is loaded. The page is the main forums page which displays the list of forums, list of moderators, last post made in each forum, # of replies and threads in each forum etc.
[url]http://hondaprelude.com/webboard[/url]

There are 3 main procs that retrieve this information. The information such as the forum_ids, forum_names and list of moderators is not very dynamic and is only executed once when the application is restarted and stored in an application variable. Here are the queries for the more dynamic data:

SELECT COUNT(*)
,forum_id
FROM forum_root
GROUP BY forum_id
ORDER BY forum_id ASC

-------------------------

SELECT COUNT(*)
,forum_id
FROM forum_children
GROUP BY forum_id
ORDER BY forum_id ASC

-----------------------

SELECT '<a href="javascript:openProfile(' + CONVERT(VARCHAR(6),fr.latest_post_member_id) + ');">' + fr.latest_post_nickname + '</a>' AS profile_link
,fr.latest_post_date
,fr.root_id
,fr.latest_post_child_id
FROM forum_root fr
JOIN (SELECT MAX(latest_post_date) AS LatestPostDate,forum_id
FROM forum_root fr
GROUP BY forum_id) AS LatestPostByForum
ON fr.forum_id = LatestPostByForum.forum_id
AND fr.Latest_Post_Date = LatestPostByForum.LatestPostDate
ORDER BY fr.forum_id ASC

The forum_root table contains about 82,000 records. The forum_children table contains about 260,000 records. The proc that does a count on the forum_children table seems to be the meanest by far in terms of CPU usage. Does anyone have any suggestions on improving performance or have a link to a good article on creating and maintaining indexes?

These are the only indexes on the forum_children table:

CREATE INDEX [child_id] ON [dbo].[forum_children]([child_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [root_id] ON [dbo].[forum_children]([root_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [subject] ON [dbo].[forum_children]([subject]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

Sorry for the long post. I am a rookie when it comes to SQL and the site is getting so much traffic now that my lack of experience shows. Thanks as always for all your help!

Chris

EDIT: Just saw that there is no index on forum_id. I just added that for the root table and the children table and it did seem to help some.

Edited by - cbrinson on 05/02/2002 16:15:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources