Delay in inserting records continuously into the Database

Dear All,
I am having a process which continuously inserts a record into a single
table with the following table schema:
CREATE TABLE [MessageLog] (
[MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
[SegmentCode] [smallint] NULL ,
[SetID] [smallint] NULL ,
[ScripCode] [int] NULL ,
[ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [int] NULL ,
[OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeStamp1] [int] NULL ,
[MessageType] [int] NULL ,
[ValidityTime] [int] NULL ,
[Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I have written an SP to update this table from my code. Most of the time to
execute this SP it is taking around 1 to 2 ms, but some time (10 out of 5000
inserts) it takes around 75, 100 or even 200 milliseconds also.
Database Properites:
General:
Maintanence Plan -> None
Collation Name -> SQL_Latin1_General_CP1_C1_AS
Both for Data file and Log File:
Automatic Grow File -> Set to Yes
File Growth -> By 10 Percent
Maximum File Size -> Unrestricted File Growth
Recovery Model -> Simple
Auto Update Statistics -> Set to True
Auto Create Statistics -> Set to True
All the other settings are set to false
Compatibility Level -> Database Compatibility Level 80
Can any one help me out to get a consistent of 1 to 2 ms every time?
Thanks and Regards,
PeriSome possible causes may be:
1)The logic organising data and then passes it to SQL Server to insert
2)triggers on the table
3)Complex foreign keys
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Peri" <Peri@.newsgroups.nospam> wrote in message
news:eIuYzCcaGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Dear All,
> I am having a process which continuously inserts a record into a single
> table with the following table schema:
> CREATE TABLE [MessageLog] (
> [MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [SegmentCode] [smallint] NULL ,
> [SetID] [smallint] NULL ,
> [ScripCode] [int] NULL ,
> [ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserID] [int] NULL ,
> [OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TimeStamp1] [int] NULL ,
> [MessageType] [int] NULL ,
> [ValidityTime] [int] NULL ,
> [Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> I have written an SP to update this table from my code. Most of the time
to
> execute this SP it is taking around 1 to 2 ms, but some time (10 out of
5000
> inserts) it takes around 75, 100 or even 200 milliseconds also.
> Database Properites:
> General:
> Maintanence Plan -> None
> Collation Name -> SQL_Latin1_General_CP1_C1_AS
> Both for Data file and Log File:
> Automatic Grow File -> Set to Yes
> File Growth -> By 10 Percent
> Maximum File Size -> Unrestricted File Growth
> Recovery Model -> Simple
> Auto Update Statistics -> Set to True
> Auto Create Statistics -> Set to True
> All the other settings are set to false
> Compatibility Level -> Database Compatibility Level 80
> Can any one help me out to get a consistent of 1 to 2 ms every time?
> Thanks and Regards,
> Peri
>|||Hi Peri,
Thank you for using MSDN Managed Newsgroup Support.
You may need to use the SQL Profiler to trace your SP running and check why
your SQL execution will take more time than usual. Basically, you may check
the CPU usage, disk reads and writes event.
Please note that:
Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.
Thank you for your patience and understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peri" <Peri@.newsgroups.nospam>
>Subject: Delay in inserting records continuously into the Database
>Date: Thu, 27 Apr 2006 11:36:14 +0530
>Lines: 49
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>Message-ID: <eIuYzCcaGHA.3740@.TK2MSFTNGP03.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: btnl-tn-dsl-dynamic-177.10.144.59.touchtelindia.net
59.144.10.177
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:595672
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Dear All,
>I am having a process which continuously inserts a record into a single
>table with the following table schema:
>CREATE TABLE [MessageLog] (
> [MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [SegmentCode] [smallint] NULL ,
> [SetID] [smallint] NULL ,
> [ScripCode] [int] NULL ,
> [ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserID] [int] NULL ,
> [OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TimeStamp1] [int] NULL ,
> [MessageType] [int] NULL ,
> [ValidityTime] [int] NULL ,
> [Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
>I have written an SP to update this table from my code. Most of the time
to
>execute this SP it is taking around 1 to 2 ms, but some time (10 out of
5000
>inserts) it takes around 75, 100 or even 200 milliseconds also.
>Database Properites:
>General:
>Maintanence Plan -> None
>Collation Name -> SQL_Latin1_General_CP1_C1_AS
>Both for Data file and Log File:
>Automatic Grow File -> Set to Yes
>File Growth -> By 10 Percent
>Maximum File Size -> Unrestricted File Growth
>Recovery Model -> Simple
>Auto Update Statistics -> Set to True
>Auto Create Statistics -> Set to True
>All the other settings are set to false
>Compatibility Level -> Database Compatibility Level 80
>Can any one help me out to get a consistent of 1 to 2 ms every time?
>Thanks and Regards,
>Peri
>
>|||On Thu, 27 Apr 2006 11:36:14 +0530, "Peri" <Peri@.newsgroups.nospam>
wrote:

>Most of the time to
>execute this SP it is taking around 1 to 2 ms, but some time (10 out of 500
0
>inserts) it takes around 75, 100 or even 200 milliseconds also.
Perhaps the delay is waiting for more disl file space to be allocated,
either data or log. Log in particular can be a problem as the process
must wait for completion, and the log is always growing (and growing,
and growing...)
Roy Harvey
Beacon Falls, CT