Discrepancy in insertion

[fniles]

I am using VB6, ADO and SQL Server 2005 as the database.

I have a stored procedure like this:
alter PROCEDURE [dbo].[INSERT_INTO_HistTradesOrigtest]
@ID int output,
@Floor varchar(50) = NULL,
@Order varchar(50) = NULL
AS
insert into HistTradesOrig
([Floor],[Order])
values
(@Floor,@Order)

This is the table
CREATE TABLE [dbo].[HistTradesOrigTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LOG] [smallint] NULL,
[Floor] [varchar](50) NULL,
[order] [varchar](50) NOT NULL,
CONSTRAINT [PK__HistTradesOrig__6FE99F9F] PRIMARY KEY CLUSTERED
(
[order] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SQL Server is on our server, and the test database that I am using only has
30 rows in the table that I am inserting, and I am the only one who is using
the database.
There are other databases that are pretty actively being used on that SQL
Server.
On my machine when I call that stored procedure from the program, sometimes
it takes 1 milliseconds to insert the record to the table, but sometimes it
takes 16 miliseconds to do it.
What could cause the time difference in the insertion, when I am the only
user in the database ?

Thank you for your help.

Set m_cmd = New ADODB.Command
Set m_cmd.ActiveConnection = adoCon
m_cmd.CommandType = adCmdStoredProc
m_cmd.CommandText = sCommandText
m_sCommandText = sCommandText
m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
adParamOutput, 12)
m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
adParamInput, 50)
m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
adParamInput, 50)
m_cmd.Parameters("Floor").Value = ""
m_cmd.Parameters("ORDER").Value = "1"
m_cmd.Execute

[Erland Sommarskog]

Post by fniles
SQL Server is on our server, and the test database that I am using only
has 30 rows in the table that I am inserting, and I am the only one who
is using the database.
There are other databases that are pretty actively being used on that SQL
Server.
On my machine when I call that stored procedure from the program,
sometimes it takes 1 milliseconds to insert the record to the table, but
sometimes it takes 16 miliseconds to do it.
What could cause the time difference in the insertion, when I am the only
user in the database ?

It is not clear you measure the time, but the resolution in low milliseconds
is not that good in SQL Server, but usually you see the time to bump 13 or
16 ms at a time.

Also, even if you are alone in the database, other load on the machine
may still incur a delay for your process, as schedulers are common across
all databases.

[Mike Williams]

Discrepancy in insertion

Well, yes. Most of us have that problem from time to time ;-)

On my machine when I call that stored procedure from
the program, sometimes it takes 1 milliseconds to insert
the record to the table, but sometimes it takes
16 miliseconds to do it.

How are you measuring the time? Are you using the VB Timer function or
perhaps the timeGetTime API or something else that relies on the same timer?
If so then you will find that on most machines the best resolution is about
15 or 16 milliseconds per "tick". You would have the same problem if you
were using the multimedia timer (timeGetTime) at its default setting on most
machines, which is also about 15 or 16 milliseconds. If you are using any of
those metods, or anything similar, then you need to use soemthign with a
better resolution. You can get a resolution of about 1 millisecond on most
machines from timeGetTime if you first set the resolution whe your program
starts using timeBeginPeriod. There are other ways of getting timings with a
resolution in the order of one or two microseconds, but the one millisecond
resolution using timeGetTime in conjunction with timeBeginPeriod shoud be
good enough for you. You need to use timeBeginPeriod (just once) at least 20
milliseconds or so before the rest of your timing code starts using
timeGetTime for real timing purposes (otherwise your first timing will not
have the desired resolution), and you need to use a matching timeEndPeriod
when you no longer need the specified resolution (although in these days of
very fast machines it doesn't really make any difference if you have the one
millisecond resolution in effect for the entire period your program is
running).

Mike

[Mike Williams]

Post by Mike Williams
How are you measuring the time? Are you using the
VB Timer function or perhaps the timeGetTime API
or something else that relies on the same timer?

Oops. Slight typo there! I of course meant to say, "How are you measuring
the time? Are you using the VB Timer function or perhaps the GetTickCount
API . . .". The timeGetTime function, as explained in my previous response,
uses the multimedia timer and is okay for resolutions of about one
millisecond on almost all machines, as long as you use timeBeginPeriod
first.

Mike

[Dan Guzman]

Post by fniles
There are other databases that are pretty actively being used on that SQL
Server.

Then the other activity can influence performance of your application. Keep
in mind that other databases on the instance use the same processors, memory
and disk (assuming your database files are not physically isolated from the
others) so other concurrent activity can affect timings. Factors like data
and log on the same spindle can cause spikes in update query duration.

But, as Erland and Mike mentioned, timer resolution is a consideration here.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by fniles
I am using VB6, ADO and SQL Server 2005 as the database.
alter PROCEDURE [dbo].[INSERT_INTO_HistTradesOrigtest]
@ID int output,
@Floor varchar(50) = NULL,
@Order varchar(50) = NULL
AS
insert into HistTradesOrig
([Floor],[Order])
values
This is the table
CREATE TABLE [dbo].[HistTradesOrigTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LOG] [smallint] NULL,
[Floor] [varchar](50) NULL,
[order] [varchar](50) NOT NULL,
CONSTRAINT [PK__HistTradesOrig__6FE99F9F] PRIMARY KEY CLUSTERED
(
[order] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SQL Server is on our server, and the test database that I am using only
has 30 rows in the table that I am inserting, and I am the only one who is
using the database.
There are other databases that are pretty actively being used on that SQL
Server.
On my machine when I call that stored procedure from the program,
sometimes it takes 1 milliseconds to insert the record to the table, but
sometimes it takes 16 miliseconds to do it.
What could cause the time difference in the insertion, when I am the only
user in the database ?
Thank you for your help.
Set m_cmd = New ADODB.Command
Set m_cmd.ActiveConnection = adoCon
m_cmd.CommandType = adCmdStoredProc
m_cmd.CommandText = sCommandText
m_sCommandText = sCommandText
m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
adParamOutput, 12)
m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
adParamInput, 50)
m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
adParamInput, 50)
m_cmd.Parameters("Floor").Value = ""
m_cmd.Parameters("ORDER").Value = "1"
m_cmd.Execute