Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not sure you're faster without cursors Expand / Collapse
Author
Message
Posted Wednesday, December 24, 2008 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 4:30 AM
Points: 1, Visits: 17
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 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 = 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, @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 datetime, @e datetime

select Id, FirstName, LastName, BirthDate into #t
from tblPeople
order by Id

set @s = 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, @e )

I think, i will continue to use cursors, it's just 107 times faster

/Pascal
Post #625393
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse