InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:11:25 GMT20http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxI agree that cursors are a drag on performance but what a way to go about avoiding them! A Count(*) and a delete for every row of the table, REALLY?You can achieve the same effect far more efficiently by declaring a temporary table with an Identity column as Primary Key. Then all you need to do is increment a variable and do select where Id = @Iterator, you can test @@Rowcount or value IS NULL to manage the loop.Fri, 24 Dec 2010 02:41:22 GMTpeter_nightingalehttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxHaving read through umpteen threads on here like this, I think that every article on SSC should have a disclaimer: "All concepts, views, and results in this Article are as shown in good faith by the author. They may not be applicable to your situation, servers or configuration, and may in fact prove detrimental in certain border cases. Your mileage may vary. Your house may be repossessed if you do not keep up your mortgage repayments. If you have any empirical evidence of cases where this method is worse than than the obvious solution, please post them with results.":^)Wed, 23 Jun 2010 07:47:17 GMTNick W*http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]Tom.Thomson (6/22/2010)[/b][hr][quote][b]YSLGuru (6/21/2010)[/b][hr][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?[/quote]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.[/quote]Hi folks, it's all good, no harm done, but I am a little older and wiser in this regard. I've submitted a preamble to the article (left the article intact as that was what generated so much conversation in the first place) and hopefully it should get updated soon. Cheers.Wed, 23 Jun 2010 07:38:08 GMTGabyYYZhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]YSLGuru (6/21/2010)[/b][hr][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?[/quote]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.Tue, 22 Jun 2010 13:19:53 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxIt 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.Tue, 22 Jun 2010 12:39:48 GMTjberg-604007http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxHere'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.[code="sql"]-- 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 rangeset @dbid = 4 -- start of the range (in this case to skip system databases)select @dbid = min(database_id)from sys.databaseswhere database_id > @dbidwhile (@dbid is not null) -- this condition here can be customized -- to change at a specific valuebegin -- 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 > @dbidend[/code]Tue, 22 Jun 2010 09:20:03 GMTAldo Gonzalezhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]Nick Walton (6/22/2010)[/b][hr]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.HTHN.p.s. Adding PRIMARY KEY to your temp table (for small loops) does improve things a [i]lot[/i]. [/quote]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.Tue, 22 Jun 2010 08:13:10 GMTGabyYYZhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxIf 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.HTHN.p.s. Adding PRIMARY KEY to your temp table (for small loops) does improve things a [i]lot[/i]. Tue, 22 Jun 2010 07:30:34 GMTNick W*http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]Jeff Moden (6/22/2010)[/b][hr][quote][b]YSLGuru (6/21/2010)[/b][hr]Wow Tom, you really need to whip up on this guy bad ey?[/quote]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.[/quote]Thanks Jeff and YSLGuru...lessons definitely learned, experienced does that to you, especially since I initially wrote the article. Nuff said. :-)Tue, 22 Jun 2010 07:23:28 GMTGabyYYZhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]YSLGuru (6/21/2010)[/b][hr]Wow Tom, you really need to whip up on this guy bad ey?[/quote]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.Tue, 22 Jun 2010 07:18:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]Tom.Thomson (6/19/2010)[/b][hr][quote][b]GabyYYZ (6/18/2010)[/b][hr]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,[b]and[/b]2. The answer to 90% of SQL Server related questions is "It Depends!". I'm learning not to say [b]never[/b] or [b]always[/b] 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. :-)[/quote]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.[/quote]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.Mon, 21 Jun 2010 14:25:07 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxIsn'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"Mon, 21 Jun 2010 10:48:45 GMTrmattawayhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]GabyYYZ (6/18/2010)[/b][hr]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,[b]and[/b]2. The answer to 90% of SQL Server related questions is "It Depends!". I'm learning not to say [b]never[/b] or [b]always[/b] 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. :-)[/quote]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.Sat, 19 Jun 2010 17:04:14 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxWhy use cursors in any guise? You have a giant pile of data and you want to go through it and take an action on each row? Sometimes the right tool for the job is a set-based dump truck that moves the whole pile in one shot. With proper indexes and ideally tuned hardware, multi-million row tables can be manipulated in mere moments. Other times there might be some perception that one spoonful of data must be examined at a time. We talk about making the spoon-by-spoon operation fast enough to compete with the dump truck and sometimes it does win. However I wonder if there is another approach; let's stop sifting through piles of data every time we want to answer a question. When the same question is asked daily/weekly/monthly, we should anticipate that and plan a good solution in advance.Transactional systems generally are small numbers of rows per transaction and the status changes occur in response to transaction activity. Rather than throwing all these transactions into a heap for later analysis and reporting (a resource intensive task) would it be possible to architect data storage so the follow-on from the row-affecting action flows in response to each single point of activity? Is there a modified schema that could allow the reporting system to be as up-to-date in nearly-realtime as the these bulk-load and brute force operations currently take to complete? Should we maybe consider that the choice is not simply RBAR vs Set-based but could be a different kind of data structure altogether? How many OLTP systems are strained beyond necessity because OLAP is not as well documented and familiar to those who have to "get it done; today." Seems like the price we pay to get it done today may be to have to work the same way tomorrow too.Fri, 18 Jun 2010 21:38:53 GMTMike Dougherty-384281http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxEvery SQL book I ever read or course I ever took had somewhere in it the admonition to avoid cursors wherever possible. Back in the dawn of SQL time (talking 6.5 here - and yes, I am that old), I used WHILE loops exclusively.Then another developer pointed out to me that if looping through a collection of records, a WHILE loop has to execute the query for every iteration through the loop. However, a cursor would execute the query once and then traverse through the result. Frankly, I didn't believe and we argued rather hotly about it until he produced a lab that proved performance in a cursor was far better than in a WHILE loop - at least for the type of coding we were doing.After a bit of pondering, I came to the conclusion that all the bad press about cursors was really that SQL works best in set-based operations. But sometimes, you have to iterate through a result set of some kind. In some circumstances a cursor would work best and others a WHILE loop.For me, the moral of the story is that good and bad is relative when it comes to coding logic and you have to keep an open mind ready to experiment to find the best solution.Fri, 18 Jun 2010 15:37:59 GMTdlander525http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]TheSQLGuru (6/18/2010)[/b][hr]Myself and a number of other MVPs have been hitting Microsoft up to get them to make cursors perform much better on the platform, which would be a benefit to MANY organizations out there, especially those with legacy and/or ISV apps that use them heavily and always will. Keep your fingers crossed![/quote]I'd actually hate to see that happen to such a nice declarative language. ;-) It would be much nicer if they'd spend the time return some of the functionality they took away betwee 2k and 2k5 and to fix some of the... ummmm.... "incomplete" functionality they've added both is SSMS and T-SQL.Fri, 18 Jun 2010 13:02:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxTo all who are pro-Cursor fans that for whatever reason just refuse to listen to or simply can’t grasp the concept and therefore choose to ignore the advice that many here (like Jeff Moden and Phil Factor) have tried to offer up; all I can say is keep doing just like you are. Please keep using cursors as much as you can. This way you’ll generate more job opportunities for the rest of us who are smart enough to take advice and listen to those who know what they are talking about. In addition, kudos to Gaby for at least trying and being a good sport in spite of the level of nasty replies he got form some. It’s fair easier to be an armchair DBA/critic then to actually put something out on the site for review. It is the nasty comments and non-constructive comments that help keep away what might be good articles from users who are hesitant to write something b/c they’ve seen the hack jobs some will do with their ugly posts. Constructive criticism is good; acting like a self-righteous you know what does nothing but let the rest know what to expect from you. Fri, 18 Jun 2010 11:41:11 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]Phil Factor (6/18/2010)[/b][hr][p]I thought that it was a good idea to re-feature the article, because it provoked an interesting debate, and I enjoyed reading through all the points that were made. It is a fascinating thread.[/p][p]Maybe, with re-featured articles, we ought to pop in an editorial note for the benefit of newer subscribers saying 'be sure to read the discussion that followed'. We old-timers tend to instinctively click through, particularly if the article looks at all controversial.[/p][p]Anyway, many thanks to Gaby Abed, and all the others who take the time and effort to contribute articles. Long may they continue to do so! (and thanks to Steve for re-featuring this article)[/p][/quote]Phil (or anyone else),I did not catch this article the first time around and did not realize it wasn't current till I got the page in the discussion that referenced Hugo’s blog. I'm only a little less then half way thru the pages and as much as I’d like to read them all I do have to do some work during the day and so I have a question or 2 I was hoping you could answer.First and foremost is did JAR (Jonathan AC Roberts) and others like him, but mostly JAR ever post anything faintly resembling an apology to Jeff Moden for the harsh bashing and outright rude posts they made even after Jeff’s replies were always more than polite and far more so then those he replied to deserved? I couldn't believe the level of harshness and negativity some of these posters like JAR made during the discussion. It was as if they were trying to be as big a jac…, well you get the point. I'm also curious if the [quote]“Hey there’s nothing wrong with a Cursor or 2 or 3 or everywhere in your code”[/quote] crowd ever got-it and clued in to what Jeff and you and several others were trying hard to convey about Cursors in the SQL language?ThanksFri, 18 Jun 2010 11:33:16 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxOkay, so it's obviously been awhile since the original article was published (my first I think). Things have changed in my DBA development skills (and some things have not):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,[b]and[/b]2. The answer to 90% of SQL Server related questions is "It Depends!". I'm learning not to say [b]never[/b] or [b]always[/b] 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. :-)Thank you.Fri, 18 Jun 2010 11:25:42 GMTGabyYYZhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]James Stephens (6/18/2010)[/b][hr]While I agree that in general cursors can adversely affect performance, I disagree with the conclusion that there is "always" a way to use a set operation rather than a cursor, and that that way is always better.[/quote]I tend to agree with you. I've tended to tell people "never use a cursor....until you have to"The thing is, many people reach for this tool way too early in a problem. There are some great set-based solutions, which can be harder to understand, but it's worth investing some time in here. Many problems can be better solved without cursors. And that's how you build some better experience.I do agree with you there are times that the code gets so complex I think a cursor would make things much easier. IF it doesn't severely impact performance. That's the trick. You need to be very careful about cursors as data sizes grow.Fri, 18 Jun 2010 11:23:35 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxThanks for republishing this article. I was around when it was publish and didn't follow the discussion. The discussion was definately informative and worth reading through.Fri, 18 Jun 2010 10:40:12 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxWhile I agree that in general cursors can adversely affect performance, I disagree with the conclusion that there is "always" a way to use a set operation rather than a cursor, and that that way is always better.In the elbow-patched tweed-blazer world of acedamia you may hear the "always" part a lot--and it's certainly a good idea to get new coders started off in good habits--but in the trenches of the real world, the mantra leans more towards "Just because you can, doesn't mean you should".It's been mentioned before that a procedural top-down technique with a cursor can add readability, maintainability and ease of understanding. That's true, and in cases where fretting over a few nanoseconds isn't the top priority, I prefer readability. I won't get into specific examples but they do exist...in the real world if not in the classroom.That's not to say i champion cursors. I just don't think that doing sql acrobatics just to prove a point is necessarily the right thing in *all cases all the time*. Yes--early on, when I was 'a bad person' and used cursors because I didn't have the chops to write a proper sql statement to do the job, I absolutely fit into the target of this article. A few years back, after reading one of the plethora of similar articles, I made a conscious effort to root out cursors, and it helped be greatly in learning and understanding sql.However, there are some procedures in which I still use cursors because the set operation was so filled with case/when's, And/Or's, convoluted parenthesis, etc, that the sql statement was unreadable and unmaintainable.Just another viewpoint...--JimFri, 18 Jun 2010 10:19:34 GMTJames Stephenshttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[p]I thought that it was a good idea to re-feature the article, because it provoked an interesting debate, and I enjoyed reading through all the points that were made. It is a fascinating thread.[/p][p]Maybe, with re-featured articles, we ought to pop in an editorial note for the benefit of newer subscribers saying 'be sure to read the discussion that followed'. We old-timers tend to instinctively click through, particularly if the article looks at all controversial.[/p][p]Anyway, many thanks to Gaby Abed, and all the others who take the time and effort to contribute articles. Long may they continue to do so! (and thanks to Steve for re-featuring this article)[/p]Fri, 18 Jun 2010 10:17:52 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxIn 18 months there are probably 100,000 new people that have received the newsletter and not seen this article. We republish highly rated and highly read articles over time so that other people see them.The original date for the article is included on the front page, and in the article. We could look at adding a note to the newsletter as well. Asking the author to update this is a possibility as well.Fri, 18 Jun 2010 10:05:41 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]carie dobson (6/18/2010)[/b][hr][quote][b]jcrawf02 (6/18/2010)[/b][hr]That's quite harsh. I disagree, the discussion on this thread is very valuable.[/quote]Sorry - harsh was not the intention. Perhaps what I should have said is the article should not be recycled [u]as is[/u]. The point was made by another poster in the thread, and you back it up, that you have to read pages and pages of discussion to get the value of the article. I agree with you, the content in the thread is definitely valuable. I admit I didn't read it all (there's quite a bit) but there were many good points made. Would be nice to have that boiled down and put back into the article, removing all the commentary about who came up with what idea, etc. Ok, so my re-worded opinion is that the article should be revised and updated to include the good points & suggestions made by the SSC community before being referenced by the newsletter again. :-)[/quote]That's a good idea, thanks for clarifying! :-)Fri, 18 Jun 2010 09:49:17 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxGreat Stuff Gaby. I am currently working on importing NASTY ms access database tables into SQL 2008. Do to the total lack of planning on the part of the original developer, I have 25 separate databases, all with one table, all with duplicate data. I have been creating cursors to loop the data and add the New Customers, add their vehicle info and flag that they got a letter.To say the least, this gives me an Idea on how to make the process a little faster.Thanks again!RobFri, 18 Jun 2010 09:47:03 GMTRobert Dean Waibel, Jrhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]jcrawf02 (6/18/2010)[/b][hr]That's quite harsh. I disagree, the discussion on this thread is very valuable.[/quote]Sorry - harsh was not the intention. Perhaps what I should have said is the article should not be recycled [u]as is[/u]. The point was made by another poster in the thread, and you back it up, that you have to read pages and pages of discussion to get the value of the article. I agree with you, the content in the thread is definitely valuable. I admit I didn't read it all (there's quite a bit) but there were many good points made. Would be nice to have that boiled down and put back into the article, removing all the commentary about who came up with what idea, etc. Ok, so my re-worded opinion is that the article should be revised and updated to include the good points & suggestions made by the SSC community before being referenced by the newsletter again. :-)Fri, 18 Jun 2010 09:43:49 GMTcarie dobsonhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxThe discussion is the valuable part.Perhaps re-published material should include an intro that explains why it is being republished.Fri, 18 Jun 2010 09:41:08 GMTbwillsie-842793http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]carie dobson (6/18/2010)[/b][hr]IMO there's a time and place for everything. There's no need to be weaned, just educated. Cursors are not evil - just a tool in the toolbox.[b]As for the article, I think it should be pulled from the site so it's not recycled as newsletter content again[/b].[/quote]That's quite harsh. I disagree, the discussion on this thread is very valuable.Fri, 18 Jun 2010 09:17:38 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxGaby,Congrats on putting yourself out there!BrianFri, 18 Jun 2010 08:51:37 GMTbbop1322http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxIMO there's a time and place for everything. There's no need to be weaned, just educated. Cursors are not evil - just a tool in the toolbox.As for the article, I think it should be pulled from the site so it's not recycled as newsletter content again.Fri, 18 Jun 2010 08:25:54 GMTcarie dobsonhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxcould have been better if it would have had a better example of replaceing something with a set operation instead of a while loop. from what i've been told while loops perform close to cursors if not worse. i haven't any proof to prove or deny that though.Fri, 18 Jun 2010 07:38:23 GMTdforckhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxHmm interesting. I read the article thinking it is current because it showed up this morning in my email.Instead it turns out to be 18 months old!I'll grant you the subject is important, but reposting it would have made more sense if it had a caveat advising readers that it was a topic that bore (bear?) repeating. And, that the discussion comments were critical to understanding the topic.Unfortunately there are probably a number of people who read the article and never bothered to read any further. Fri, 18 Jun 2010 07:29:25 GMTbwillsie-842793http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxThanks Gaby -- and thanks to all who added their replies... lots of good ideas have been shared. I'll mark this one as a keeper!Fri, 18 Jun 2010 07:21:00 GMTGAFhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]TheSQLGuru (6/18/2010)[/b][hr]Myself and a number of other MVPs have been hitting Microsoft up to get them to make cursors perform much better on the platform, which would be a benefit to MANY organizations out there, especially those with legacy and/or ISV apps that use them heavily and always will. Keep your fingers crossed![/quote]...but then I'll have to actually learn how to use them....:-P:hehe:Fri, 18 Jun 2010 07:15:40 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxThere's a funny thing about absolutes "there is 'always' a better alternative to using [cursors]", they're rarely absolute. I tend to use the @table route as much as I can, but it's not always the better route. So when it comes to which one to use...it depends...One of the things I noticed though, is that when you've got a person who's use to coding in some other programming language using the WHILE loop is so much easier for them to get than trying to dive into a cursor and understand the craziness/cost.Fri, 18 Jun 2010 07:13:44 GMTgraymerhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]GabyYYZ (12/24/2008)[/b][hr]Sigh...The while loop I used was only a proof of concept, and was designed to show people the mechanics of breaking out of a cursor mindset. They are both still loops, but the overhead of a cursor is much higher.If you can get better performance out of a set based query, by all means do so. If you have to use a loop, try the more conventional ones. If you have an issue using the more typical while loops, you may have to use cursors. Perhaps I didn't make it that clear in the article.I'll focus on a more realistic scenario next time.Merry Christma-Hannu-Kwanza Everybody![/quote]Hi Gaby,I agree with this statement to a certain extent. what I dont agree with is the statement that while loops have less overhead than cursors. This is not always true and even not true in MOST cases. The fact of the matter is the performance of the cursor depends on the type of cursor you create. For example, had you declared a local static cursor, you would probably see more comparable results. My friend Brad Schulz has written a great blog series on the innards of cursors, which I highly recommend.[url]http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html[/url][url]http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-2.html [/url][url]http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-3.html [/url]Fri, 18 Jun 2010 07:06:54 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspxMyself and a number of other MVPs have been hitting Microsoft up to get them to make cursors perform much better on the platform, which would be a benefit to MANY organizations out there, especially those with legacy and/or ISV apps that use them heavily and always will. Keep your fingers crossed!Fri, 18 Jun 2010 07:03:48 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx[quote][b]OCTom (6/18/2010)[/b][hr]"Remember... if you're gonna practice the piano, no sense in intentionally hitting the wrong keys. :D "It DOES make sense to intentionally hit the wrong keys. It teaches you what's good and what's bad. And, you may create something great. Experimentation is important.[/quote]Taken out of context here, it does not make sense if you're trying to play the piece that is on the page. In context of the comment, he is referring to coding in a correct manner, if you intentionally code poorly, you're not 'experimenting', and you're not going to 'create something great', you'll do the exact opposite.If you're playing a literal piano, and you want to have a sound like Thelonius Monk, then by all means, intentionally hit the wrong keys (but only the ones that are really right by being wrong).Fri, 18 Jun 2010 06:58:09 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic625172-1373-1.aspx"Remember... if you're gonna practice the piano, no sense in intentionally hitting the wrong keys. :D "It DOES make sense to intentionally hit the wrong keys. It teaches you what's good and what's bad. And, you may create something great. Experimentation is important.Fri, 18 Jun 2010 06:29:34 GMTOCTom