January 10, 2008 at 2:18 pm
We have a basic Content Management system that is currently in use which contains about 10 pages or so.
I have a very basic SP (GetContent) which is passed the ContentID of a record as a GUID, and returns the page content as ntext. The query takes about 4 seconds to execute.
I'm looking for advice on the best way to proceed. I'm assuming that I need to add an index, but are there any other suggestions, or should I just create and index, and if so, should it just be on the GUID?
Thanks in advance for your help.
Stephen
January 10, 2008 at 2:24 pm
What's your table definition, what keys/indexes are on it now?
Can you provide examples of the queries ran against it currently?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 10, 2008 at 3:15 pm
Here's the table structure:
CREATE TABLE [dbo].[ContentManagement](
[ContentID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ContentManagement_ContentID] DEFAULT (newid()),
[Content] [ntext] NOT NULL,
[PageName] [nvarchar](200) NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_ContentManagement_CreatedOn] DEFAULT (getdate()),
[CreatedBy] [int] NOT NULL,
[UpdatedOn] [datetime] NULL,
[UpdatedBy] [int] NULL,
[LastViewed] [datetime] NULL,
[Views] [int] NOT NULL CONSTRAINT [DF_ContentManagement_Views] DEFAULT (0),
[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_ContentManagement_IsDeleted] DEFAULT (0),
[IsDownMaint] [bit] NOT NULL CONSTRAINT [DF_ContentManagement_IsDownMaint] DEFAULT (1),
CONSTRAINT [PK_ContentManagement] PRIMARY KEY CLUSTERED
(
[ContentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And here's the SP that we use to get the content:
CREATE PROCEDURE [dbo].[GetContent]
(
@ContentID uniqueidentifier,
@IPAddress nvarchar(15) = NULL,
@CustomerID int = NULL
)
AS
IF (SELECT Count(*) FROM dbo.ContentManagement WHERE ContentID = @ContentID AND IsDeleted = 0) = 1
BEGIN
SELECT Content, IsDownMaint FROM dbo.ContentManagement WHERE ContentID = @ContentID;
UPDATE dbo.ContentManagement SET LastViewed = GetDate(), Views = (Views + 1) WHERE ContentID = @ContentID;
END
ELSE
BEGIN
SELECT 0;
END
Thanks.
Stephen
January 10, 2008 at 3:23 pm
Try to execute
SELECT DATALENGTH(Content), IsDownMaint
FROM dbo.ContentManagement
WHERE ContentID = @ContentID
Probably it's the size of ntext thing what gives you hard time.
Because you've got PK on the column, no more indexes needed for this query.
_____________
Code for TallyGenerator
January 10, 2008 at 5:30 pm
The length of the largest field is 256946. But I've worked on CMS systems w/ larger pages before. Any ideas?
January 10, 2008 at 6:04 pm
How long does it take to execute the version of query I posted?
_____________
Code for TallyGenerator
January 10, 2008 at 8:17 pm
If you run SET STATISTICS TIME ON prior to running the stored procedure, do the time breakdowns indicate anything useful?
Ray Mond
http://www.sqlinspect.com
In-depth query analysis for SQL Server
January 10, 2008 at 8:56 pm
It takes 4581 ms to execute the query and 1 ms to execute the same w/ DataLength(Content)
Stephen
January 10, 2008 at 9:57 pm
So, it gives you the answer:
All the time is spent on uploading data to front end.
There is nothing to improve in SQL Server performance.
Improve your upload channel.
Or don't store blobs in database.
_____________
Code for TallyGenerator
January 10, 2008 at 10:12 pm
How do you know that? The 4581 ms is in the Query Analyzer. I'm not measuring time to the page itself. Why does it take so long to return this data?
This isn't unnecessary data, these a content pages which are frequently viewed for informational purposes.
Any other thoughts?
Stephen
January 10, 2008 at 10:31 pm
I think what Sergiv meant was that all the time was spent transmitting the data from the database to the database client, in your case its Query Analyzer. Probably should have also ran DBCC DROPCLEANBUFFERS prior to running the DataLength query just to clear the buffer cache, otherwise the 1ms response might just be because SQL Server was reading from the cache.
Ray Mond
http://www.sqlinspect.com
In-depth query analysis for SQL Server
January 11, 2008 at 4:16 am
How do you know that?
I use to think.
The 4581 ms is in the Query Analyzer.
QA is a front end application.
The only difference essential (in our case) from yours is the copyright.
It also connects to an instance of SQL Server and has to upload data in order to display it.
I'm not measuring time to the page itself. Why does it take so long to return this data?
Streaming.
To return DATALENGTH SQL Server has to read the ntext value.
But it passes to UI application only 4 byte integer value.
It takes less than 1 ms.
In your query it reads the same value but now it needs to pass whole thing to UI.
A little thinking effort could bring you to a conclusion that it takes 4.5 seconds to pass those up to 256 megabytes to front end application.
This isn't unnecessary data, these a content pages which are frequently viewed for informational purposes.
Is it Rembrandt? Because for Picasso you could reduce resolution. In his drawings point is not in details.
Any other thoughts?
Using database for storing data instead of dumping files would increase quality of your project in many aspects.
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply