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
@serviceRequestId 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