August 25, 2003 at 11:04 pm
I was asked how I would go about looping through a rowset without using a cursor. Everything that I have read says creating a cursor and looping through using FETCH is the only solution. Am I correct?
August 25, 2003 at 11:14 pm
-- List of all user tables, one at a time
Set NoCount On
Declare @id Int
Select @id=Min(Id)
From SysObjects
Where OBJECTPROPERTY(ID,'IsUserTable')=1
While @id Is Not Null
Begin
-- Do some more operations with this table
Select 'Table ',Object_Name(@id)
-- Get the next table
Select @id=Min(Id) From SysObjects
Where OBJECTPROPERTY(ID,'IsUserTable')=1 And
id>@id
End
Set NoCount Off
August 26, 2003 at 1:41 am
Create a memory table with an identity column and the pk of the table.
below a small example :
declare @counter as integer
declare @maxcount as integer -- Used for upper bounds
declare @keyval as integer -- Counter variable
declare @memtable table -- Memory table
( mykey int identity,
otherkey int
)
insert @memtable (otherkey)
select id from sysobjects -- Insert the keys into the memtable
set @counter = 1
select @maxcount=max(mykey) -- Determine the upper bound
from @memtable
while @counter <= @maxcount -- Begin loop
begin
select @keyval=otherkey -- Retrieve the Key value
from @memtable
where mykey = @counter
select * from sysobjects where id = @keyval
set @counter = @counter + 1
end
August 26, 2003 at 8:12 am
So are the two methods that are mentioned better than using a CURSOR in any way?
Speed?
Memory Usage?
Especially dealing in a high volume (10 of millions) of records.
August 26, 2003 at 8:20 am
In previous versions of MS-SQL, cursors were such poor performers than almost any other method would have been preferred. The last set of tests we ran on SQL 2000 indicated that it is extremely good...especially in extremely large result sets. Extremely high numbers of rows affect all SQL users (not just the one making the call) when put into temporary tables. Loops in a SELECT MIN() scenario aren't too bad as long as there is an indexed value. Anyway - I wouldn't avoid a cursor based on what you have said here.
My $0.02
Guarddata-
August 26, 2003 at 9:19 am
I agree with Guarddata...
Ther is now way of us knowing in advance what will be the optimum solution.
So there is nothing left to do then build a small prototype of each.......
And try it yourself. I have seen situations where cursors where faster and situations where cursosr are slower...
So there is not a definitife answer. But the bigger the ResultSets that you have to handle the more you would lean to a cursor....
I guess there is a kind of break eaven point with the cursor overhead and a temp (memory)table.....
August 26, 2003 at 10:09 am
I actually noticed and undocumented stored procedure called xp_execresultset.
You can't see the code since it's an extended stored procedure. (xprepl.dll) My guess is that it runs a cursor behind the scenes.
Anybody familier with it?
I did find this article:
http://www.rac4sql.net/xp_execresultset.asp
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
August 27, 2003 at 4:18 am
I had a second thought....
If you are really working with millions of rows.... Is iterating through every record the best way then.
My guess is that a set based operation will always be faster. Isn't there a way to make this a normal update / select statement. Even if it means coding 20 queries instead of one cursor....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply