Alternative To Cursors?

Question

Alternative To Cursors?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
November 12, 2010 at 1:50 pm

#228787

Just wanted to throw out a question to developers on what method do they use to loop around different objects.I typically use cursors, was wondering if there is a better way or any new concept in 2008 where i can avoid cursors. Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

If you really do need to loop through a dataset, then a cursor is a perfectly valid way to do it. The problem with cursors isn't that, it's that most uses of them can be resolved by much more efficient queries that work faster.

If you're trying to avoid cursors for the sake of avoiding them, then CLR has some good looping methods in it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

John Rowan SSC Guru Points: 56440 More actions · Answer 2

You say to "loop through different objects". Can you clarify this? As Gus said, if you really do have to loop, a cursor will do that, but most people think they have to loop when they really don't.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

WayneS SSC Guru Points: 95460 More actions · Answer 3

Just to reiterate what the others have said: 99% of all loops in SQL can be avoided with proper set-based programming. If you're willing to learn, we're willing to help you learn better ways to do things. Remember - SQL is a set-based programming language, not a loop-based one (that processes things Row-By-Agonizing-Row).

People here have seen loop-based code that runs in hours reduced to simple set-based code that runs in seconds.

Interested?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

sqldba_icon (11/12/2010)
Just wanted to throw out a question to developers on what method do they use to loop around different objects.I typically use cursors, was wondering if there is a better way or any new concept in 2008 where i can avoid cursors. Thanks

I assume you mean possibly looping around to get to different databases or tables for common maintenance solutions. If that's true, then there is no need to avoid cursors. A While Loop will still be necessary if you use a Temp Table and you're not using it for RBAR. Use a nice, tight, Forward Only, Read Only, Static cursor. Yes, there are ways to avoid cursors for such things using concatenation of commands but the cursor won't be the resource hog or performance problem that a RBAR cursor would be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

curious_sqldba

SSC-Dedicated

Points: 36494

November 13, 2010 at 7:07 pm

#1250399

sure. where can i find the details?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

sqldba_icon (11/13/2010)
sure. where can i find the details?

Google? Books Online? I'm not trying to be a smart guy here... that's where I'd look.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 7

Jeff Moden (11/12/2010)
sqldba_icon (11/12/2010)
Just wanted to throw out a question to developers on what method do they use to loop around different objects.I typically use cursors, was wondering if there is a better way or any new concept in 2008 where i can avoid cursors. Thanks
I assume you mean possibly looping around to get to different databases or tables for common maintenance solutions. If that's true, then there is no need to avoid cursors. A While Loop will still be necessary if you use a Temp Table and you're not using it for RBAR. Use a nice, tight, Forward Only, Read Only, Static cursor. Yes, there are ways to avoid cursors for such things using concatenation of commands but the cursor won't be the resource hog or performance problem that a RBAR cursor would be.

Jeff: "Fast_Forward" does all the "forward only", "read only", "static" options all in one. Per MSDN/BOL, it also includes some performance optimizations. (You probably already know this, but I wasn't sure.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

andrew gothard SSChampion Points: 12314 More actions · Answer 8

Jeff Moden (11/14/2010)
sqldba_icon (11/13/2010)
sure. where can i find the details?
Google? Books Online? I'm not trying to be a smart guy here... that's where I'd look.

Not trying to be the smart guy here either, but I think Jeff's being rather modest. Try looking up some of his articles (or Gail 'Gila Monster' Shaw's). I'd suggest their posts as well - but as there are some 10 billion of them (approx) I'd sort out a granular search based on your requirements and see what pops up. If you had time to read them all - you'd be well sorted - but also close to retirement <g>

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

WayneS SSC Guru Points: 95460 More actions · Answer 9

sqldba_icon (11/13/2010)
sure. where can i find the details?

I would suggest starting off by reading this article[/url] by MVP Jeff Moden.

I would also suggest the two "15 Ways to Lose Your Cursor" articles here[/url].

I would then search for "cursor" and "loop" in the forums on this site, and see how people have been helped to get rid of their cursors/loops and replace them with set-based solutions.

If you run into problems, post back. But first, please read the first article in my signature, and post the requested data, and the code you're trying to change. You'll get several people jumping in to help you learn.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

GSquared (11/15/2010)
Jeff: "Fast_Forward" does all the "forward only", "read only", "static" options all in one. Per MSDN/BOL, it also includes some performance optimizations. (You probably already know this, but I wasn't sure.)

I haven't tested in quite a while (I only use cursors to show how slow they are) but it used to be that listing the options separately produced faster code. That was way back in SQL Server 7. I believe it was Kevin Boles that showed me that but I'm not 100% sure. Guess I need to write another test.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

juliekenny SSCommitted Points: 1558 More actions · Answer 11

I have used a table to hold the loop counter. Running the whole statement was too much for the memory available so it was broken down into processing by year (created about 6 chunks). The relevant years put in a table, top 1 processed then removed from the year counter table - looped until the counter table was empty.

I'm not saying this is pretty or clever but it is another way to do it and I knew less. Today I would use a cursor.

BTW - this was no way a rbar, just cut it down to a few million rows a go.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 12

GSquared (11/15/2010)
Jeff Moden (11/12/2010)
sqldba_icon (11/12/2010)
Just wanted to throw out a question to developers on what method do they use to loop around different objects.I typically use cursors, was wondering if there is a better way or any new concept in 2008 where i can avoid cursors. Thanks
I assume you mean possibly looping around to get to different databases or tables for common maintenance solutions. If that's true, then there is no need to avoid cursors. A While Loop will still be necessary if you use a Temp Table and you're not using it for RBAR. Use a nice, tight, Forward Only, Read Only, Static cursor. Yes, there are ways to avoid cursors for such things using concatenation of commands but the cursor won't be the resource hog or performance problem that a RBAR cursor would be.
Jeff: "Fast_Forward" does all the "forward only", "read only", "static" options all in one. Per MSDN/BOL, it also includes some performance optimizations. (You probably already know this, but I wasn't sure.)

Not quite -- STATIC isn't included. Per BOL....

FAST_FORWARD
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

Hugo Kornelis wrote a marvelous article comparing the performance of a number of different cursor options and discovered that conventional wisdom about this is often wrong. Noteworthy was his finding:

Based on all tests, it turns out that the best performance is achieved by specifying a STATIC cursor. I would add the LOCAL, FORWARD_ONLY, and READ_ONLY options for documentation purposes, but they make no performance difference.

and

If you think that the FAST_FORWARD option results in the fastest possible performance, think again. I have not found one single test case where it was faster than, or even as fast as, a STATIC cursor.

And yes, his first conclusion was "Always try to replace the cursor by a set-based equivalent first."

Rich

GSquared SSC Guru Points: 260824 More actions · Answer 13

I just did a simple speed test on a do-nothing cursor.

DECLARE c CURSOR STATIC FOR

SELECT NAME

FROM sys.columns;

DECLARE @DB sysname;

OPEN c;

FETCH NEXT FROM c

INTO @DB;

DECLARE @Start DATETIME;

SET @Start = GETDATE();

WHILE @@FETCH_STATUS = 0

FETCH NEXT FROM c

INTO @DB;

SELECT DATEDIFF(millisecond, @Start, GETDATE());

CLOSE c;

DEALLOCATE c;

It's not a full test, but I ran default, static, and fast-forward, 10 times each. Default (no options) took between 13 and 16 milliseconds for each run. Static took 3 to 10, with 6 being the most common. Fast-forward took between 3 and 10, with 3 being the most common. Real tests would be more complex and on much more data.

As per Hugo's blog entry:

Disclaimer: All results presented here are only valid for my test cases (as presented below) on my test data (a copy of the SalesOrderDetail table in the AdventureWorks sample database), on my machine (a desktop with 2GB of memory, a dual-core processor, running SQL Server 2005 SP2), and with my workload (just myself, and only the test scripts were active). If your situation is different, for instance if the table will not fit in cache, if the database is heavily accessed by competing processes, or if virtually any other variable changes, you really ought to perform your own test if you want to squeeze everything out of your cursor. And also consider that many options are included to achieve other goals than performance, so you may not be able to use all options without breaking something.

I ran different test data on a different machine, and got different results.

In my general speed testing, I've found fast-forward to be as fast or faster than other options more often that not. That's not universal, but nothing is.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

patelmohamad SSCommitted Points: 1873 More actions · Answer 14

HI,

Avoid Cursor , Using While loop

Just go through it

----- Solution ----- 1

If Identity Column Does not exist in the table

DECLARE @iIndex INT ,@RowsCount int

SELECT Identity(INT,1,1) AS Sr_No, FieldName1, FieldName2, FieldName3,…………… FieldNameN

INTO #Temp_Table

FROM Table_Name

---- WHERE

ORDER BY FieldNameX

SELECT @iIndex = 1

SELECT @RowsCount = @@RowCount

or

SELECT @RowsCount = Count(Sr_No) FROM #Temp_Table

WHILE @iIndex <= @RowsCount

BEGIN --- 2

if EXISTS(SELECT Sr_No FROM #Temp_Table WHERE Sr_No = @iIndex)

BEGIN

SELECT @FieldName1 = FieldName1,@FieldName2 = FieldName2,@FieldName3= FieldName3, @FieldName4= FieldName4 …….N

FROM #Temp_Table WHERE Sr_No = @iIndex

--- Perform your Task

END

SELECT @iIndex = @iIndex + 1

END --- 2

DROP TABLE #Temp_Table

----- Solution ----- 2

If Identity Column exist in the table

DECLARE @FieldName1 DataType, @FieldName2 DataType ……….. @FieldNameN DataType

DECLARE @iIndex INT ,@RowsCount int

SELECT IdentityColumn_Name ,FieldName2 ,FieldName3 ,FieldName4 ,FieldName4 ……….,FieldNameN

INTO #Temp_Table

FROM Table_Name

---- WHERE

ORDER BY FieldNameX

SELECT TOP 1 @RowsCount = IdentityColumn_Name FROM #Temp_Table ORDER BY IdentityColumn_Name DESC

SELECT TOP 1 @iIndex = IdentityColumn_Name FROM #Temp_Table ORDER BY IdentityColumn_Name

WHILE @iIndex <= @RowsCount

BEGIN

if EXISTS(SELECT IdentityColumn_Name FROM #Temp_Table WHERE IdentityColumn_Name = @iIndex)

BEGIN

SELECT @FieldName1 = FieldName1,@FieldName2 = FieldName2,@FieldName3= FieldName3, @FieldName4= FieldName4 …….N

FROM #Temp_Table WHERE IdentityColumn_Name = @iIndex

--- Perform your Task

END

SELECT @iIndex = @iIndex + 1

END

DROP TABLE #Temp_Table

Patel Mohamad