Please give me a replacement for a Cursor
-
Sub : I would like to have a replacement for a Cursor.
It should be other than the use of Table variables or
Temporary Tables.
Is there any other methods available.
regards
shown
-
Post the table definition, some sample data and the expected output from the query. We can't help you without that.
-
If there is a unique id, then it is easy:
while (@id is not null)
select @id = min(id) from table where id > @id
and continue until you get null.
-
That's nice but how about we show him how to use the set based approach that he obviously doesn't know??
-
Not sure I know that either, so I look forward to your reply...
-
Will have to look for his reply first
. -
Mr Jesper,
I think this is the technique what U have meant.
****************************************************
eg:-
SELECT @id=MIN(id) FROM TABLE1
SET @minid=@id
WHILE @minid IS NOT NULL
BEGIN
------
-------
-------
SELECT @minid=MIN(id) FROM TABLE1 WHERE id > @minid
END
***********************************************************
If this is the technique if a table is very big that
doesn't help i feel. Every time U will have to check
the minimum of that table for manipulations.
Actually I know 3 techniques which are replacement for the
Cursors.
1) Using Temp tables
2) Using Table Variables
3) Finding the MIN value and manipulating row by row
I wanted some other technique which can be actually used on
big tables so that the manipulations does work faster.
regards
shown
-
Thanx Shown for this input, but I'd like to stress out that these methods are only LAST RESORTS, you must first try to find the SET BASED approach that will run faster 99.999% of the time compared to these work-arounds.
-
You assumption is correct
I added a simple "select @minid" to the code above and constructed a similar cursor example. Then I ran the two algorithms on a table with 77000 rows. They both completed in 48 secs (you need an index on id, of course).
Are you looking for something even faster, or is there a different reason for not using cursors?
-
Here's my thinking : Don't use cursors, ever. Then if you find a situation where a cursor is faster than a set based approach or there's no set based approach (some rare admin task), then go for it. But I assure you that it's not gonna happen often.
-
Please give me some examples regarding
"What did u mean by this SET BASED APPROACH"
regards
shown
-
while (@id is not null)
begin
select @id = min(id) from table where id > @id
Update Table set SomeField = (Select Value from Table2 where id = @Id) where id = @Id
end
Now run this on a table with 100k rows.
Should take 2-20+ minutes.
Now run this :
Update T1 SET T1.SomeField = T2.Value from dbo.Table T1 inner join dbo.Table2 T2 on T1.id = T2.id
Should take between 1 and 20 seconds.
-
Ok, Does it mean that it's better to prefer
SET BASED APPROACH above all other methods.
shown
-
As I said, research only for procedural approach when no set based approach exists (or works at reasonable speed). Sometimes it's faster to use a cursor, but it's just 0.001% of the time.
You have to switch your thinking when programming in sql. Don't do things one step at the time, but do all steps in one pass
. -
Nice way to finish.
Anyway thank U very much for the help and to everyone involved.
regards
shown
Viewing 15 posts - 1 through 15 (of 70 total)
You must be logged in to reply to this topic. Login to reply