The thought of scaling either RBAR to millions of rows fills my heart with sorrow.

But, making me even more sad is the fact that yes, we've replaced a cursor, but actually with a method that's even more inefficient. Say you did have 1,000,000 rows, for the cursor you would have the read of the data and cursor through it. For the other way, you would have the creation of the table (be it temporary or variable), the insert into it, and 2,000,000 selects and 1,000,000 deletes.

Try the following three queries - one of which is a cursor, one of which uses this method with a temp table, and one of which uses this method with a table variable. The query works on 100,000 rows and executes reasonably meaningless code so that we're testing the performance of the iteration method, rather than the performance of what is being executed.

Note also that in the temp table / table variable methods I have added a clustering PK to increase the performance of these methods - i got bored of waiting for the queries to finish without!

On my server, the cursor takes 12.562 seconds to execute, the temp table method takes 33.093 seconds to execute, and the table variable method takes 32.218 seconds to exeucte.

Cursor method:

SET NOCOUNT ON

declare @query varchar(100), @id int

declare BadCursor Cursor for

select top 100000 ID from [tblLogTransactions]

open BadCursor

fetch next from BadCursor into @id

while @@fetch_status = 0

begin

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

exec(@query)

fetch next from BadCursor into @id

end

close BadCursor

deallocate BadCursor

go

Temp Table method:

SET NOCOUNT ON

declare @query varchar(100), @id int

CREATE TABLE #muchWorseProblem (ID int primary key clustered)

INSERT INTO #muchWorseProblem (ID)

select top 100000 ID from [tblLogTransactions]

while (select count(*) from #muchWorseProblem) > 0

begin

select top 1 @id = ID from #muchWorseProblem

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

exec(@query)

delete from #muchWorseProblem where id = @id

end

Table Variable method:

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]

while (select count(*) from @muchWorseProblem) > 0

begin

select top 1 @id = ID from @muchWorseProblem

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

exec(@query)

delete from @muchWorseProblem where id = @id

end

:blink: