Speeding up retrieval for CMS system

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!!!

Re: Speeding up retrieval for CMS system

programmingcodeATjards.com wrote:
> 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.

Ooooh! 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.

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()) & "
"

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

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

.... some code
Response.Write datediff("s",t, Now()) & "
"
%>
--
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"