473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Slow Insert - 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.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
1 1666
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: cricketunes | last post by:
Hi everyone :) I have made a form to get values and insert them to a database. I have 1) A file conn.php that does the initial connections 2) The form file form.php 3) Action file...
5
by: Shay | last post by:
essentially I am trying to do some counts based on some assumptions in the recordset. So I get the RS back, put the values into a variable, move to the next record in the RS and compare what is in...
16
by: mamo74 | last post by:
Hello. I am administering a SQL Server (Enterprise Edition on Windows 2003) from some month and can't understand what is going on in the latest week (when the db grow a lot). The DB is around...
4
by: Bob Sparks | last post by:
Hello I am running an insert that takes an awfully long time compared to the same workload on Oracle. It takes 44 minutes to insert 2.5 million records. It takes less time to insert the records...
4
by: sherkozmo | last post by:
SQL2000 - AccessXP I built an adp file with a stored procedure from SQL as follows: SELECT * FROM Z_mis_sjk_job_code_access WHERE job_code=@JobCode UNION ALL SELECT * FROM...
29
by: Geoff Jones | last post by:
Hi All I hope you'll forgive me for posting this here (I've also posted to ado site but with no response so far) as I'm urgently after a solution. Can anybody help me? I'm updating a table on...
3
by: Mario Soto | last post by:
Hi. i hava a postresql 7.4.2 in a production server. tha machine is a Pentium IV 2,6 GHZ AND 1 GB IN RAM with lINUX RH 9.0. The postresql.conf say: ...
8
by: shenanwei | last post by:
I have 2 same windows machine, same instance configure and Database , all run DB2 UDB V8.1.5 Test 1 : create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL GENERATED ALWAYS AS...
9
by: dan | last post by:
within a loop i am building a sql insert statement to run against my (programatically created) mdb. it works but it seems unreasonably SLOW! Sorry, dont have the code here but the jist is very...
21
by: lesperancer | last post by:
I've got an access97 reporting mdb that pulls data (77,000 rows) from a sql server table into a local table to run reports if the local table is part of the reporting MDB, the insert statement...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.