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

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • peanut

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

    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.getMessages ForSR
    @srid numeric

    AS

    select *
    from SRMessages with (nolock)
    where srid= @srid
    order by SRMessageID
    GO
    SET QUOTED_IDENTIFI ER 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,@t imestamp,@type, @initiator,@dat a)

    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_Gene ral_CP1_CI_AS NOT
    NULL ,
    [Data] [ntext] COLLATE SQL_Latin1_Gene ral_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

Working...