Insert running slow

Are inserts really slow in 2005 or am I doing something stupid?
Here's the table:
CREATE TABLE [dbo].[Tickets](
[ticket] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[data] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[added] [datetime] NOT NULL CONSTRAINT [DF_Tickets_added] DEFAULT
(getdate()),
[lastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED
(
[ticket] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I wrote this stored procedure:
ALTER PROCEDURE [dbo].[CreateTicket]
@.ticket varchar(20) OUTPUT,
@.data varchar(2000)
AS
DECLARE @.key varchar(20)
DECLARE @.len int
DECLARE @.added bit
DECLARE @.cypher varchar(52)
SET NOCOUNT ON;
SET @.added=0
SET @.cypher='abcdefghijklmnopqrstuvwxyzABCDE
FGHIJKLMNOPQRSTUVWXYZ0123456789'
WHILE @.added=0
BEGIN
SELECT @.key='', @.len=20
WHILE @.len>0
BEGIN
-- The following line is the SLOW one!!!
SET @.key = @.key + SUBSTRING(@.cypher, CAST(FLOOR(RAND()*52) AS int)+1,1)
SET @.len = @.len -1
END
IF NOT EXISTS(SELECT 1 FROM Tickets WHERE ticket=@.key)
BEGIN
INSERT INTO Tickets (ticket, data,lastupdated) VALUES(@.key, @.data,GETDATE())
SET @.ticket = @.key
SET @.added = 1
END
END
And then used this to test it's speed:
DECLARE @.ticket varchar(20)
DECLARE @.sec datetime
DECLARE @.cnt int
TRUNCATE TABLE Tickets
SET @.cnt = 0
SET @.sec = DATEADD(second, 1, GETDATE())
WHILE GETDATE()<@.sec
BEGIN
EXEC CreateTicket @.ticket, 'this is a test'
SET @.cnt=@.cnt + 1
END
PRINT @.cnt
When I run this on SQL 2000, I get roughly 3000 records a second. When I
run it against 2005 I get roughly 160 records per second! The statement tha
t
is taking all the time in 2005 is the insert statement!
On 2005 if I comment it out I can execute 8,600ish loops per second. If it
isn't commented out I run 160ish.
On 2000 if I comment it out I can execute 5,900 loops per second, If it
isn't commented out I run 3,000ish.
Is inserting really that expensive or am I missing some knob I forgot to tur
n?Never mind. It appears there's something wrong with the server I was testin
g
on. Testing on another server I got 5,600ish inserts per second. What's
really weird though is the box that's performing slowing is a faster box tha
n
either of the other two with faster disks. Guess it's time to reinstall :)|||Before reinstalling, I would check perfmon and profiler and see what is
actually taking so long. It might be something easy to fix (or it might be
that reinstalling would cause the same performance problems.) A reinstall
might be in order, but unless that is really easy to do for you, it is
probably just something in how something is set up.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Larry Charlton" <LarryCharlton@.discussions.microsoft.com> wrote in message
news:A378F5F2-63BC-4FE0-AD6D-DFBB2952BB06@.microsoft.com...
> Never mind. It appears there's something wrong with the server I was
> testing
> on. Testing on another server I got 5,600ish inserts per second. What's
> really weird though is the box that's performing slowing is a faster box
> than
> either of the other two with faster disks. Guess it's time to reinstall
> :)
>