December 24, 2008 at 8:01 am
Hello,
I did some performance tests on the following table
CREATE TABLE [dbo].[tblPeople](
[Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](100) NULL,
[BirthDate] [datetime] NULL,
CONSTRAINT [PK_tblPeople] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I've created 10.000 rows
declare @i int
set @i=1
while @i<10000
begin
insert tblPeople ( FirstName, LastName, BirthDate )
values ( 'FirstName ' + str(@i), 'LastName ' + str(@i), getdate() )
set @i=@i+1
end
With the cursor, it takes 343 ms
declare @id int
declare @firstname varchar(100)
declare @lastname varchar(100)
declare @birthdate datetime
declare @s-2 datetime, @e datetime
--declare c cursor for
declare c cursor local forward_only for
select Id, FirstName, LastName, BirthDate
from tblPeople
order by Id
set @s-2 = getdate()
open c
while 1=1
begin
fetch next from c into
@Id, @FirstName, @LastName, @BirthDate
if @@fetch_status != 0 break
end
close c
deallocate c
set @e =getdate()
print datediff( ms, @s-2, @e )
With Gaby's approach, it takes 36690 ms !!
declare @id int
declare @firstname varchar(100)
declare @lastname varchar(100)
declare @birthdate datetime
declare @s-2 datetime, @e datetime
select Id, FirstName, LastName, BirthDate into #t
from tblPeople
order by Id
set @s-2 = getdate()
while 1=1
begin
select top 1
@Id=Id, @FirstName=FirstName, @LastName=LastName, @BirthDate=BirthDate
from #t
order by Id
if @@rowcount = 0
break
delete #t where Id=@Id
end
drop table #t
set @e =getdate()
print datediff( ms, @s-2, @e )
I think, i will continue to use cursors, it's just 107 times faster
/Pascal
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply