By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Help with Slow INSERT - MS SQL 2000 - Help?!?

P: n/a
I have a stored procedure that can take over 5 seconds to add simple
data. Please give any advice on optimizing?

Here are the details:

***** Access Info ******
~10 rows are added to the table every second
the table is read ~20 times a second (with no lock)
***** Stored Procedures *****
-------
CREATE PROCEDURE dbo.getMessagesForSR
@srid numeric

AS

select *
from SRMessages with (nolock)
where srid= @srid
order by SRMessageID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------
CREATE PROCEDURE dbo.addMessage

@sridnumeric,
@timestamp bigint,
@type smallint,
@initiator nvarchar (100),
@data ntext

AS

INSERT INTO SRMessages VALUES(@srid,@timestamp,@type,@initiator,@data)

GO
---------

***** The Table *****

CREATE TABLE [dbo].[SRMessages] (
[SRMessageId] [bigint] IDENTITY (1, 1) NOT NULL ,
[srid] [bigint] NOT NULL ,
[Timestamp] [bigint] NOT NULL ,
[Type] [smallint] NOT NULL ,
[Initiator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Data] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[SRMessages] ADD
CONSTRAINT [PK_SRMessages] PRIMARY KEY NONCLUSTERED
(
[SRMessageId]
) ON [PRIMARY]
GO

CREATE INDEX [SRMessages2] ON [dbo].[SRMessages]([srid]) ON [PRIMARY]
GO

Jul 23 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.