help with extreme slow site

[Guoqi Zheng]

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_GetMsgPerPage
@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(MsgContent),
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

 

[Lucas Tam]

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?

Do the page numbers change often?

If not, you could cache the information in an Application Variable on the
start of an application.

Otherwise, try adding some indexes to your columns. Indexes often help
speed up queries.

 

[Guoqi Zheng]

Thanks for the reply.

Page nr. does not change very often, twice per day I think.

I am thinking it may have something to do with the cache of Stored
procedure. For lazy reason, I have a few site with exactly the same code
only use different database and they all use SA login. I am thinking that
maybe that confuse the database.

Because when I only clicking one site, the speed is always very fast. When I
click other site and come back, the speed is 20 seconds again.

Is this possible, any one got an idea?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

 

[John Saunders]

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.

When you say "the first hit", do you mean the first request after
application restart? If so, it's to be expected, as that's when compilation
is taking place. The performance of subsequent requests is much more
interesting.