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 12345»»»

Please give me a replacement for a Cursor Expand / Collapse
Author
Message
Posted Thursday, July 7, 2005 6:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:34 AM
Points: 30, Visits: 31

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 #197948
Posted Thursday, July 7, 2005 6:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
Post the table definition, some sample data and the expected output from the query. We can't help you without that.
Post #197961
Posted Thursday, July 7, 2005 6:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

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.

Post #197966
Posted Thursday, July 7, 2005 6:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
That's nice but how about we show him how to use the set based approach that he obviously doesn't know??
Post #197968
Posted Thursday, July 7, 2005 7:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33
Not sure I know that either, so I look forward to your reply...
Post #197977
Posted Thursday, July 7, 2005 7:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
Will have to look for his reply first .
Post #197987
Posted Thursday, July 7, 2005 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:34 AM
Points: 30, Visits: 31
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
Post #197998
Posted Thursday, July 7, 2005 8:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
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.
Post #198022
Posted Thursday, July 7, 2005 8:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

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?

Post #198030
Posted Thursday, July 7, 2005 8:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
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.
Post #198044
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse