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

Slow Insert - 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
@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

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
peanut (ms*********@yahoo.com) writes:
I have a stored procedure that can take over 5 seconds to add simple
data. Please give any advice on optimizing?
Are there any triggers on the table?

What is the average size of the ntext column you insert?

Would be possible for you supply more than one row at a time? You can
bundle many rows in an XML document and insert all at once. This is likely
to be more effective than inserting one row at a time. But this assumes
that all rows to insert are available.
CREATE PROCEDURE dbo.getMessagesForSR
@serviceRequestId numeric

AS

select *
from SRMessages with (nolock)
where srid= srid


Is this WHERE clause a typo?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.