|
Subject:
|
rise speed for "sp_Forums_GetTopicsByPage"
|
|
Posted By:
|
chinahyf
|
Post Date:
|
12/16/2005 2:57:54 AM
|
This is a StoreProcedure in Forums module.
It used to get topics by @PageNumber, @PageSize.
.....
INSERT INTO #TempTopics
(
TopicID,
Subject,
AddedDate,
TopicReplies,
TopicLastReplyDate,
TopicLastPostDate,
MemberID,
MemberName,
Email,
ShowEmail
)
SELECT
TopicID,
Subject,
AddedDate,
TopicReplies,
TopicLastReplyDate,
TopicLastPostDate,
MemberID,
MemberName,
Email,
ShowEmail
FROM
v_Forums_Topics WHERE ForumID = @ForumID ORDER BY TopicLastPostDate Desc
....
I think if there have many records from views,you select all every page,not very good.So I think if can use "TOP".
Last I found use "SET ROWCOUNT " can do it.
Now ,it looks like this:
.....
-- fill the temp table with all the topics for the
-- specified forum retrieved from the v_Forums_Topics view
DECLARE @TopSize int
SET @TopSize = (@PageNumber* @PageSize)
SET ROWCOUNT @TopSize
INSERT INTO #TempTopics
(
TopicID,
Subject,
AddedDate,
TopicReplies,
TopicLastReplyDate,
TopicLastPostDate,
MemberID,
MemberName,
Email,
ShowEmail
)
SELECT
TopicID,
Subject,
AddedDate,
TopicReplies,
TopicLastReplyDate,
TopicLastPostDate,
MemberID,
MemberName,
Email,
ShowEmail
FROM
v_Forums_Topics WHERE ForumID = @ForumID ORDER BY TopicLastPostDate Desc
SET ROWCOUNT 0
-- declare two variables to calculate the range of records to extract for the specified page
.....
The same as "sp_Forums_GetRepliesByPage"
I think this can raise your speed.
I am a Chinese,my English not good!
Thank you!
|
|
Reply By:
|
englere
|
Reply Date:
|
12/19/2005 7:54:39 AM
|
That's a good idea!
There's another trick to this that avoids using "top". The idea is that you have a good primary key column, and you know RowCount will limit the rows you retrieve, so you can just remember the highest primary key value that you retrieved the first time, and make sure your next fetch takes only primary key values greater than that.
This article explains it:
http://www.codeproject.com/aspnet/PagingLarge.asp
SQL Server 2005 has a new method of paging results that is even better. This will be covered in the second edition.
|
|
Reply By:
|
chinahyf
|
Reply Date:
|
12/20/2005 1:44:56 AM
|
I want to see your next version book,it really a good book for me,thank you!
|
|
