Cursors Be Gone!

SET NOCOUNT ON
declare @query varchar(100), @id int
DECLARE @muchWorseProblem TABLE (ID int primary key clustered)

INSERT INTO @muchWorseProblem (ID)
select top 100000 ID from [tblLogTransactions]

declare @id2 int;
declare @i int;
set @i = 0;

while 1=1
begin
select top 1 @id2 = ID from @muchWorseProblem

if @id2 is null
break;

select @query = 'declare @i int; set @i = ' + convert(varchar,@id2)
exec(@query)
delete from @muchWorseProblem where id = @id2
set @id2 = null
end

SET NOCOUNT ON
declare @query varchar(100), @id int, @rowNum int
DECLARE @muchWorseProblem TABLE (RowNum int IDENTITY(1, 1) primary key clustered, ID int)

INSERT INTO @muchWorseProblem (ID)
select top 100000 ID from [tblLogTransactions]

declare @maxRowNum int
SELECT @maxRowNum = MAX(RowNum) FROM @muchWorseProblem

declare @i int;
set @i = 1;

while @i <= @maxRowNum
begin
select @id = ID from @muchWorseProblem WHERE rowNum = @i

select @query = 'declare @i int; set @i = ' + convert(varchar,@id)
exec(@query)

set @i = @i + 1
end

Matt Whitfield (12/24/2008)

Having modified that script a bit, and run it on my server - the time was 23.078 secs. Improved, certainly, but not better.

SET NOCOUNT ON
declare @query varchar(100), @id int
DECLARE @muchWorseProblem TABLE (ID int primary key clustered)

INSERT INTO @muchWorseProblem (ID)
select top 100000 ID from [tblLogTransactions]

declare @id2 int;
declare @i int;
set @i = 0;

while 1=1
begin
  select top 1 @id2 = ID from @muchWorseProblem
  
  if @id2 is null
        break;

  select @query = 'declare @i int; set @i = ' + convert(varchar,@id2)
  exec(@query)
  delete from @muchWorseProblem where id = @id2
        set @id2 = null
end

The quickest I could get it to go was the following, which does come in slightly quicker than a cursor, at 10.703 secs:

SET NOCOUNT ON
declare @query varchar(100), @id int, @rowNum int
DECLARE @muchWorseProblem TABLE (RowNum int IDENTITY(1, 1) primary key clustered, ID int)

INSERT INTO @muchWorseProblem (ID)
select top 100000 ID from [tblLogTransactions]

declare @maxRowNum int 
SELECT @maxRowNum = MAX(RowNum) FROM @muchWorseProblem

declare @i int;
set @i = 1;

while @i <= @maxRowNum
begin
  select @id = ID from @muchWorseProblem WHERE rowNum = @i
  
  select @query = 'declare @i int; set @i = ' + convert(varchar,@id)
  exec(@query)

  set @i = @i + 1
end

SET NOCOUNT ON
declare @query varchar(100), @id int, @rowNum int
DECLARE myCursor CURSOR FORWARD_ONLY READ_ONLY
FOR select top 100000 ID from [tblLogTransactions]

OPEN myCursor

FETCH NEXT FROM myCursor
INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN

select @query = 'declare @i int; set @i = ' + convert(varchar,@id)
exec(@query)

FETCH NEXT FROM myCursor
INTO @id

END
CLOSE myCursor
DEALLOCATE myCursor