Optimization Tips Request - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Optimization Tips Request

Author

Topic

sixside
Starting Member

12 Posts

Posted - 2006-05-26 : 23:11:04

Hello, I've been doing basic SQL/ASP Development for a while now and most apps I have built have been pretty low usage and performance was never an issue. But I have recently started a project that is getting far more usage than I have ever had experience with. I'm looking for some good advise on learning how to optimize the SQL performace part of my web app.

I use SPROCS for all DB calls.
It's MSSQL 2000 DB on a shared hosting account.

I don't know much about indexing, but from what I've read it can help if you do it right. I don't want to do it wrong though, so I'm a little hesitant.

Any help or advice would be appreciated, and any links or tutorials as well.

I've done some Google searchs but I've come here for advice since many of the people here seem to be experts ;)

Thanks!

sixside
Starting Member

12 Posts

Posted - 2006-05-27 : 00:39:32

In addition to some general advice on optimizing, I have a more specific question as well. I have a messaging system where users can leave private messages for other users.

Here is my table info:


CREATE TABLE [dbo].[Message] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL ,
[OutboxUserID] [int] NOT NULL ,
[InboxUserID] [int] NOT NULL ,
[MessageSubject] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MessageTime] [smalldatetime] NOT NULL ,
[MessageResponseID] [int] NULL 
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Message] WITH NOCHECK ADD 
	CONSTRAINT [PK_Message] PRIMARY KEY  CLUSTERED 
	(
		[MessageID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Message] ADD 
	CONSTRAINT [DF_Message_MessageTime] DEFAULT (getdate()) FOR [MessageTime]
GO


ALTER TABLE [dbo].[Message] ADD 
	CONSTRAINT [FK_MessageInbox_UserAccount] FOREIGN KEY 
	(
		[InboxUserID]
	) REFERENCES [dbo].[UserAccount] (
		[UserID]
	),
	CONSTRAINT [FK_MessageOutbox_UserAccount] FOREIGN KEY 
	(
		[OutboxUserID]
	) REFERENCES [dbo].[UserAccount] (
		[UserID]
	)
GO

This table gets queried a lot, via 3 differents SPROCS:


CREATE PROCEDURE MessageThread_LIST
@ModelUserID int,
@MemberUserID int
AS
SELECT TOP 100 PERCENT 
	MessageID, 
	InboxUserID, 
	OutboxUserID, 
	MessageSubject, 
	Message, 
	MessageTime
FROM
	dbo.Message
WHERE
	(InboxUserID = @ModelUserID) AND (InboxUserID = @MemberUserID) OR
	(InboxUserID = @ModelUserID) AND (OutboxUserID = @MemberUserID) OR
	(InboxUserID = @MemberUserID) AND (OutboxUserID = @ModelUserID) OR
	(OutboxUserID = @ModelUserID) AND (OutboxUserID = @MemberUserID)
ORDER BY 
	MessageID DESC
GO


CREATE PROCEDURE ModelOutbox_LIST
@MessageID int = NULL,
@ModelID int = NULL,
@ModelUserID int = NULL,
@MemberID int = NULL,
@MemberUserID int = NULL
AS
SELECT TOP 100 PERCENT 
	dbo.Message.MessageID, 
	dbo.ModelAccount.ModelID, 
	dbo.ModelAccount.UserID AS ModelUserID, 
	dbo.ModelAccount.ModelName, 
	dbo.ModelAccount.ModelPictureFile, 
	dbo.MemberAccount.MemberID, 
	dbo.MemberAccount.UserID AS MemberUserID, 
	UserAccount_1.UserFirstName,
	dbo.MemberAccount.MemberPictureFile, 
	dbo.Message.MessageSubject, 
	dbo.Message.Message, 
	dbo.Message.MessageTime
	
FROM         
	dbo.Message INNER JOIN
	dbo.UserAccount ON dbo.Message.OutboxUserID = dbo.UserAccount.UserID INNER JOIN
	dbo.UserAccount UserAccount_1 ON dbo.Message.InboxUserID = UserAccount_1.UserID INNER JOIN
	dbo.ModelAccount ON dbo.UserAccount.UserID = dbo.ModelAccount.UserID INNER JOIN
	dbo.MemberAccount ON UserAccount_1.UserID = dbo.MemberAccount.UserID
	
WHERE
	(dbo.Message.MessageID = COALESCE(@MessageID,dbo.Message.MessageID)) AND 
	(dbo.ModelAccount.ModelID = COALESCE(@ModelID,dbo.ModelAccount.ModelID)) AND 
	(dbo.ModelAccount.UserID = COALESCE(@ModelUserID,dbo.ModelAccount.UserID )) AND 
	(dbo.MemberAccount.MemberID = COALESCE(@MemberID,dbo.MemberAccount.MemberID)) AND
	(dbo.MemberAccount.UserID = COALESCE(@MemberUserID,dbo.MemberAccount.UserID))
ORDER BY 
	dbo.Message.MessageID DESC


CREATE PROCEDURE MemberOutbox_LIST
@MessageID int = NULL,
@ModelID int = NULL,
@ModelUserID int = NULL,
@MemberID int = NULL,
@MemberUserID int = NULL
AS
SELECT TOP 100 PERCENT 
	dbo.Message.MessageID, 
	dbo.ModelAccount.ModelID, 
	dbo.ModelAccount.UserID AS ModelUserID, 
	dbo.ModelAccount.ModelName, 
	dbo.ModelAccount.ModelPictureFile, 
	dbo.MemberAccount.MemberID, 
	dbo.MemberAccount.UserID AS MemberUserID, 
	UserAccount_1.UserFirstName,
	dbo.MemberAccount.MemberPictureFile, 
	dbo.Message.MessageSubject, 
	dbo.Message.Message, 
	dbo.Message.MessageTime
	
FROM
	dbo.Message INNER JOIN
	dbo.UserAccount ON dbo.Message.InboxUserID = dbo.UserAccount.UserID INNER JOIN
	dbo.UserAccount UserAccount_1 ON dbo.Message.OutboxUserID = UserAccount_1.UserID INNER JOIN
	dbo.ModelAccount ON dbo.UserAccount.UserID = dbo.ModelAccount.UserID INNER JOIN
	dbo.MemberAccount ON UserAccount_1.UserID = dbo.MemberAccount.UserID
	
WHERE
	(dbo.Message.MessageID = COALESCE(@MessageID,dbo.Message.MessageID)) AND 
	(dbo.ModelAccount.ModelID = COALESCE(@ModelID,dbo.ModelAccount.ModelID)) AND 
	(dbo.ModelAccount.UserID = COALESCE(@ModelUserID,dbo.ModelAccount.UserID )) AND 
	(dbo.MemberAccount.MemberID = COALESCE(@MemberID,dbo.MemberAccount.MemberID)) AND
	(dbo.MemberAccount.UserID = COALESCE(@MemberUserID,dbo.MemberAccount.UserID))
ORDER BY 
	dbo.Message.MessageID DESC

GO

Lately I have been getting a lot of Timeout expired errors from the DB when running the SPROCS.

What can I do to improve the speed and reliability of the table/queries?

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources