Speeding up retrieval for CMS system

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

programmingcodeATjards.com - 25 Aug 2006 21:00 GMT

I create a content management system using SQL Server and ASP that uses 2
tables, content and categories. Structure listed below:

content
contentID identity int
categoryID foreign key (int)
Title varchar(100)
Body text
status char(1)

category
categoryID identity int
categoryname
description
status

Now I have a database that has over 10 categories and content table has over
150 records. Problem, getting record for display has slowed tremendously.
It used to be fairly quick when obtaining records, now it takes about 1
minute (to load from webpage not sql server). I use stored procedures to
get pages using this proc :

CREATE PROC DBO.SHOWCONTENT
@CONTENTID INT, @CATEGORYID INT
AS
SELECT *
FROM CONTENT
WHERE ContentID = @CONTENTID AND CategoryID = @CATEGORYID
ORDER BY Content.categoryid, content.contentid
GO

Users access the system from links that have the ContentID and CategoryID
parameter. I have no indexes as I thought it may slow down the system since
I control the inserting of data in the system. There are default indexes
created by system for the primary key and foreign key constraints.

How can I speed up the retrieval process of this system? Any and all help
will be greatly appreciated!!!

Reply to this Message

Bob Barrows [MVP] - 25 Aug 2006 21:23 GMT

> I create a content management system using SQL Server and ASP that
> uses 2 tables, content and categories. Structure listed below:
[quoted text clipped - 14 lines]
> Now I have a database that has over 10 categories and content table
> has over 150 records.

Ooooh! 150 records? <gasp>
;-)

> Problem, getting record for display has slowed
> tremendously. It used to be fairly quick when obtaining records, now
[quoted text clipped - 13 lines]
> CategoryID parameter. I have no indexes as I thought it may slow
> down the system since I control the inserting of data in the system.

I've never heard of this as a reason for not having indexes.

> There are default indexes created by system for the primary key and
> foreign key constraints.
> How can I speed up the retrieval process of this system? Any and all
> help will be greatly appreciated!!!

Start by identifying the bottleneck. Where is the slowdown occurring?

Run your stored procedure in Query Analyzer. Is it taking a minute to
execute?

Yes? Then this is where to start. Use the Index Analyzer in QA to see if the
query would benefit from putting an index on the table. With only 150
records, it probably would not benefit, but ...

No? It executes quickly in QA? Then use SQL Profiler to trace the
procedure's execution. Does Profiler show it taking a minute to execute?

Yes? Then the problem may be "parameter sniffing": http://tinyurl.com/h7aa
If it executes quickly according to Profiler, then the problem is in the
code you are using to process the returned data. You need to identify what
part of your code is taking so long to process the data. Use Response.Write
to generate some timing statistics:

<%
dim t
t = Now
... some code
Response.Write datediff("s",t, Now()) & "<BR>"

... some code
Response.Write datediff("s",t, Now()) & "<BR>"

... some code
Response.Write datediff("s",t, Now()) & "<BR>"

... some code
Response.Write datediff("s",t, Now()) & "<BR>"
%>

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Webmaster Forum / ASP / Database Access / August 2006

Speeding up retrieval for CMS system