Dear sir,
My forum site is getting much slower. At this moment, it takes 20 seconds
for the first hit. The second hit is much quicker, I think it is because of
cache. Any idea how can I increase the speed of my ASP.NET site.
This is a forum site using VB.NET + MSDE. The database is about 500 MB. I
think the reason it is slow is because the page navigation. At first, I get
the value of how many pages in total, then write the page navigation bar,
next, I get the information of one page by a stored procedure. Below is my
store procedure.
At this moment, there are about 50,000 records in that database. Any idea
how can I make it quicker?
--
Kind regards
Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com
CREATE PROCEDURE MyProc_GetMsgPe rPage
@GroupId int,
@PageNr int,
@PageSize int
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #TempMsg
(
TempId int IDENTITY PRIMARY KEY,
IntMsgId Int,
MsgTitle varchar(200),
MsgAuthor varchar(100),
MsgShort varchar(500),
MsgDate varchar(50)
)
-- fill the temp table with all the topics for the
-- specified forum retrieved from the v_Forums_Topics view
INSERT INTO #TempMsg
(
IntMsgId,
MsgTitle,
MsgAuthor,
MsgShort,
MsgDate
)
SELECT
AutoId,
MsgTitle,
MsgAuthor,
dbo.TrimText(Ms gContent),
MsgDate
FROM
tblMsg WHERE GroupId = @Groupid AND isRef=0 Order by ServerId DESC
-- declare two variables to calculate the range of records to extract for
the specified page
DECLARE @StartId int
DECLARE @ToId int
-- calculate the first and last ID of the range of topics we need
SET @StartId = ((@PageNr - 1) * @PageSize) + 1
SET @ToID = @PageNr * @PageSize
-- select the page of records
SELECT IntMsgId, MsgTitle, MsgAuthor, MsgShort, MsgDate
FROM #TempMsg WHERE TempId >= @StartId AND TempId <= @ToId
GO
