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 «««2425262728»»

Cursors Be Gone! Expand / Collapse
Author
Message
Posted Saturday, June 19, 2010 5:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 7,799, Visits: 9,547
GabyYYZ (6/18/2010)
The two main points about this article for me, as I see it NOW, are:

1. Do I still think cursors are, how shall we say it, sub-optimal? For large amounts of data, they may not always be the best solution, but they definitely have their uses, especially if the solution can't be found with a set-based query,
and
2. The answer to 90% of SQL Server related questions is "It Depends!". I'm learning not to say never or always as much as I used to.

I've made peace with this article and will concede, especially with regards to point 2 above, to never say never, but I also will strive to look for a set-based solution whenever possible first. I hope everyone has gotten some food for thought from the discussion and that's about all I can hope from this article at this point.


Trouble is that the wrong culprit is blamed: the problem is not cursor versus no cursor but row-based versus set-based. So the question to start from is "Am I doing something essentially row-based that needs a while loop?". Most of the time the answer will be "No", so the question of a cursor never arises. On the rare occassions when the answer is "Yes", the next question is "will a cursor be the best way to handle sequencing in this while loop?", and most of the time the answer to that question will be "Yes".

The example in the article is a case where - if you decide to have a while loop - a static [or insensitive] fast_forward read_only cursor will result in clearer code as well as marginally better performance than does populating and depopulating a table variable as advocated in the article.

So the article is advocating that people (a) ask the wrong question and (b) do the wrong thing (reject the cursor in the case when it is the best solution). So I find it hard to make peace with the article (although I do tell people that cursors are to be avoided like the plague, just to encourage them to be column (as opposed to row) oriented in their thinking.


Tom
Post #940003
Posted Monday, June 21, 2010 10:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 10:08 AM
Points: 37, Visits: 90
Isn't this really an issue for those of us to know when to use and not use a cursor? There are instances where I choose to use a cursor while at other times I use a set based operation.

Some requirements are easier to put into production using a cursor. I have a computation that took me over two years of testing and trying different things before I could get it into a set based operation. I would not have wanted to hold things up just so I could dump the cursor. Now that I have had time to test, tune and validate the set based operation the results are phenomenal. Still even if I had to use a cursor the total time for the complex operation was just under 8 hours for 6.5 million records. The set based reduced the time to 45 minutes.

I believe matching your work to business requirements within a required time of completion may force us to at times to use cursors. However, if you read the Microsoft standard documentation, they really want you to use CLR procedures for heavy cursor procedural based operations.

Either way I think we all should know when to make the move. We just don't need to be lazy and never revisit our cursors just because they "work"



Post #940479
Posted Monday, June 21, 2010 2:25 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:15 AM
Points: 891, Visits: 1,553
Tom.Thomson (6/19/2010)
GabyYYZ (6/18/2010)
The two main points about this article for me, as I see it NOW, are:

1. Do I still think cursors are, how shall we say it, sub-optimal? For large amounts of data, they may not always be the best solution, but they definitely have their uses, especially if the solution can't be found with a set-based query,
and
2. The answer to 90% of SQL Server related questions is "It Depends!". I'm learning not to say never or always as much as I used to.

I've made peace with this article and will concede, especially with regards to point 2 above, to never say never, but I also will strive to look for a set-based solution whenever possible first. I hope everyone has gotten some food for thought from the discussion and that's about all I can hope from this article at this point.


Trouble is that the wrong culprit is blamed: the problem is not cursor versus no cursor but row-based versus set-based. So the question to start from is "Am I doing something essentially row-based that needs a while loop?". Most of the time the answer will be "No", so the question of a cursor never arises. On the rare occassions when the answer is "Yes", the next question is "will a cursor be the best way to handle sequencing in this while loop?", and most of the time the answer to that question will be "Yes".

The example in the article is a case where - if you decide to have a while loop - a static [or insensitive] fast_forward read_only cursor will result in clearer code as well as marginally better performance than does populating and depopulating a table variable as advocated in the article.

So the article is advocating that people (a) ask the wrong question and (b) do the wrong thing (reject the cursor in the case when it is the best solution). So I find it hard to make peace with the article (although I do tell people that cursors are to be avoided like the plague, just to encourage them to be column (as opposed to row) oriented in their thinking.



Wow Tom, you really need to whip up on this guy bad ey? Is that he must confess that every word is his story is wrong and you are his SQL superior or what? I think its safe to say by now that everyone realizes what was intended and what is best when approaching the use of Cursors instead of some standard Set based methods to solve some unique problem. Are you done yet with beating up on this article and its author?

BTW - Your analogy with asking the right question is just as potentially flawed because it assumes that the user will correctly identify the right question to ask and too often those with procedural code backgrounds will favor the curosr/loop angle because its what they know and are familiar with. The majority of problems that arise from incorrect use of cursors in our world isn't from an excess use of set based methods where cursors would be better but the complete opposite of that.

Most DBA's worth their wieght in gold push the "avoid cursors at all cost" line because they know that far too often someone who is working on SQL development who has any type of background in procedural programming is going to over use cursors; not under use them.


Kindest Regards,

Just say No to Facebook!
Post #940626
Posted Tuesday, June 22, 2010 7:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
YSLGuru (6/21/2010)
Wow Tom, you really need to whip up on this guy bad ey?


It my be just me but it doesn't look that way to me. Gaby cited some lessons learned and Tom cited another possible lesson or two. I don't see Tom picking on Gaby and I believe that Gaby sees that, as well (although I could be mistaken). It's just another dialog spawned by this particular article... not a butt chewing.

Gaby... well done on having big shoulders.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #941016
Posted Tuesday, June 22, 2010 7:23 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 644, Visits: 2,140
Jeff Moden (6/22/2010)
YSLGuru (6/21/2010)
Wow Tom, you really need to whip up on this guy bad ey?


It my be just me but it doesn't look that way to me. Gaby cited some lessons learned and Tom cited another possible lesson or two. I don't see Tom picking on Gaby and I believe that Gaby sees that, as well (although I could be mistaken). It's just another dialog spawned by this particular article... not a butt chewing.

Gaby... well done on having big shoulders.

Thanks Jeff and YSLGuru...lessons definitely learned, experienced does that to you, especially since I initially wrote the article. Nuff said.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #941022
Posted Tuesday, June 22, 2010 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 22, 2012 8:55 AM
Points: 124, Visits: 180
If I can add another more physical perspective to this, I'm afraid that I've found that the temp variable solution can be *vastly* slower than cursors. The time it takes to reserve and allocate all that memory on a server which doesn't have a massive amount spare can be considerable. Doing this on a congested server and then looking at the plan you get CLUSTERED INDEX SCAN 1%, TEMP TABLE INSERT 99%

We made the mistake of doing something similar using the exact same reasoning on a highly-trafficked SP. By the time your table gets to about 100,000 rows, that's about 400k to reserve if you only have a single column in your temp table. If you have a few hundred users, all using such a procedure on a table of over 250,000 rows, your working set bounces up and down like a yoyo, and the search can time out quite easily as pages are stolen left, right and centre. When the pages end up being stolen from the index cache, Fooom...

It's a good idea if you *really really hate cursors*, but sadly there doesn't seem to be one catch-all solution. Adding FAST_FORWARD does help a lot, I've found.

HTH

N.

p.s. Adding PRIMARY KEY to your temp table (for small loops) does improve things a lot.


Post #941033
Posted Tuesday, June 22, 2010 8:13 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 644, Visits: 2,140
Nick Walton (6/22/2010)
If I can add another more physical perspective to this, I'm afraid that I've found that the temp variable solution can be *vastly* slower than cursors. The time it takes to reserve and allocate all that memory on a server which doesn't have a massive amount spare can be considerable. Doing this on a congested server and then looking at the plan you get CLUSTERED INDEX SCAN 1%, TEMP TABLE INSERT 99%

We made the mistake of doing something similar using the exact same reasoning on a highly-trafficked SP. By the time your table gets to about 100,000 rows, that's about 400k to reserve if you only have a single column in your temp table. If you have a few hundred users, all using such a procedure on a table of over 250,000 rows, your working set bounces up and down like a yoyo, and the search can time out quite easily as pages are stolen left, right and centre. When the pages end up being stolen from the index cache, Fooom...

It's a good idea if you *really really hate cursors*, but sadly there doesn't seem to be one catch-all solution. Adding FAST_FORWARD does help a lot, I've found.

HTH

N.

p.s. Adding PRIMARY KEY to your temp table (for small loops) does improve things a lot.



I have found that when the developers or app. support folks send me a script to, say, clean data by doing a join on a temp table, the time spent creating a clustered index is well worth the time saved with the join. Many don't do that, although a few I've worked with closely know better and anticipate my "Did you create an Index?" question.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #941072
Posted Tuesday, June 22, 2010 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 8, Visits: 181
Here's another way. This one uses a range provided by a numeric column, even better if it is indexed. No need for counts or deletes.

-- we use a range provided by a numeric column
-- even better if the column is indexed

declare @query varchar(100),
@dbname sysname,
@dbid int -- to iterate through the range
set @dbid = 4 -- start of the range (in this case to skip system databases)

select @dbid = min(database_id)
from sys.databases
where database_id > @dbid

while (@dbid is not null) -- this condition here can be customized
-- to change at a specific value
begin

-- do your thing here
-- ====================================
select top 1 @dbname = dbname
from sys.databases
where database_id = @dbid

select @query = 'dbcc checkdb(' + quotename(@dbname) + ')'
exec(@query)
-- ====================================

-- fetch next value in the range
select @dbid = min(database_id)
from sys.databases
where database_id > @dbid
end

Post #941130
Posted Tuesday, June 22, 2010 12:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 23, 2013 1:01 PM
Points: 123, Visits: 293
It depends....
If you are looping thru an OLTP system, then there is locking overhead with a cursor that you want to avoid.
If you are looping thru a reporting system that is basicly read often, update daily, then a cursor could scale well, better than the temp table if your tempdb I/O is either slow or under stress.
Post #941293
Posted Tuesday, June 22, 2010 1:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 7,799, Visits: 9,547
YSLGuru (6/21/2010)
[quote][b]Wow Tom, you really need to whip up on this guy bad ey? Is that he must confess that every word is his story is wrong and you are his SQL superior or what? I think its safe to say by now that everyone realizes what was intended and what is best when approaching the use of Cursors instead of some standard Set based methods to solve some unique problem. Are you done yet with beating up on this article and its author?

No, I don't need to whip up on Gaby, I'm well aware that he understands the issues as that's made clear by his response to comments made last year, when the \rtile was first published. But I do think it's very important that people don't start by asking "cursor or no cursor" but instead "set-oriented or row by row iterative" (as one can write row by row iteration in a single query without resorting to a cursor the distinction bewteen the two questions is non-trivial). The discussion so far doesn't really make that point clea. It's also important that on those rare occassions when the answer to that starting question is "iteration" people don't discard the cursor option in favour of inferior iterative methods like the temporary table one illustrated in the article - and (unless I've missed somethiong) only Hugo had commented on that, so I thought it would be useful to make the two points together in one comment.
If Gaby thinks I was whipping up oon him I'm sure he'll let me know and if he does I will apologise profusely since that is certainly not an impression I wanted to give him.


Tom
Post #941318
« Prev Topic | Next Topic »

Add to briefcase «««2425262728»»

Permissions Expand / Collapse