March 28, 2008 at 1:19 pm
In a previous post (http://www.sqlservercentral.com/Forums/Topic475089-338-1.aspx), I asked about preferred syntax for coding a cursor-traversing loop. Most of the replies strongly disparaged the use of cursors.
So, for the edification of myself and others, when is it "right" to use a cursor?
Thanks in advance.
Chris Judge
March 28, 2008 at 1:37 pm
I'm not sure there are many cases where it is right, but I suppose there are some when they are not wrong 🙂
I occasionally will use them just for simplicity sake to accomplish jobs that run off hours, infrequently, and don't need to be efficient.
But I'm not sure I would use them anywhere where users or applications execute them regularly throughout the day.
The Redneck DBA
March 28, 2008 at 1:44 pm
cjudge (3/28/2008)
In a previous post (http://www.sqlservercentral.com/Forums/Topic475089-338-1.aspx), I asked about preferred syntax for coding a cursor-traversing loop. Most of the replies strongly disparaged the use of cursors.So, for the edification of myself and others, when is it "right" to use a cursor?
Thanks in advance.
Chris Judge
Anywhere where set-based logic cannot be used and a loop structure is required.
For example: suppose you want to loop through a bunch of customers and email some info to each of them; have to do this in a loop; a read-only, fastforward cursor is probably the best way of doing this. Sure, you can avoid a cursor in most situations and use a WHILE loop instead. I have not found a difference in performance, and if anything I think cursors are usually faster than WHILE loops.
So one scenario I can think of is when an external resource (email, file-copy etc.) is invoked.
In most other situations one should be able to construct a SET-based query in place of a cursor.
Not easy but feasible.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 28, 2008 at 1:47 pm
I should add that DBAs - myself included 🙂 - probably tend to overuse cursors in our every day work.
As Jason mentioned, as long as it does not impact server performance, it's OK.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 28, 2008 at 4:19 pm
cjudge (3/28/2008)
So, for the edification of myself and others, when is it "right" to use a cursor?
When you have to perform an operation on a number of rows and there is no way to incorporate that operation into a query.
Although there are 2, or (maybe) 3 general categories of SQL tasks (out of hundreds) that are almost always best solved through cursors, virtually none of them should come up in applications or applications development (which is to say, they are DB management/operations tasks).
The general rule of thumb that I teach people is: If you think that you need a Cursor, assume that you are wrong. This rule is correct over 90% of the time.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2008 at 4:07 am
Would any of you use the undocumented SPs sp_MSforeachdb or sp_MSforeachtable to do the iteration for them if the situation suited using these? Anyone know how these work, if they are efficient and why they are undocumented?
Thanks
Buxton69
March 31, 2008 at 4:28 am
Buxton69 (3/31/2008)
Would any of you use the undocumented SPs sp_MSforeachdb or sp_MSforeachtable to do the iteration for them if the situation suited using these? Anyone know how these work, if they are efficient and why they are undocumented?Thanks
Buxton69
sp_MSforeachdb and sp_MSforeachtable just implement a dynamically generated cursor. So from a performance point of view they are no faster than implementing your own cursor.
I use them occasionally but only for simplicity or because I'm too lazy to write my own ;).
Adding to the other comments. Generally speaking I only use cursors for DBA/administrative tasks, where the number of rows I'd be looping through are minimal and will have little impact on the system as a whole, in addition to the fact that these tasks would run during low-peak times.
In an ideal world, I'd never allow a cursor to be used for application purposes - it smacks of bad design. But then we don't live in an ideal world and I often turn up at places where this is exactly what they've done. Or even worse, a cursor is used, within which a stored procedure is called, within which another cursor is used. And they were wondering why the procedure took 3 minutes to run :o?
March 31, 2008 at 7:02 am
In almost all cases, neither a cursor nor any form of a While loop are required especially in SQL Server 2005.
Lots of folks use the guidline "when it can't be done in a set-based fashion" as to when to write a cursor or While loop. The big problem is that people give up too early on trying to find the correct set-based answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2008 at 7:14 am
Could you point me toward a good resource for examples of code without using cursors, I have some examples where you load a select statement into a temp table and then do a while loop but would like to see other examples.
Thanks
Buxton69
March 31, 2008 at 10:20 am
Buxton69 (3/31/2008)
Could you point me toward a good resource for examples of code without using cursors, I have some examples where you load a select statement into a temp table and then do a while loop but would like to see other examples.Thanks
Buxton69
Actually a WHILE loop is not necessarily better - in terms of performance - than using a cursor.
Where you get the best performance possible - at least theoretically - is when substituting a loop construct (cursor or WHILE loop) with a set-based statement.
SQL 2005 ships with a couple of new constructs that promise to eliminate the need for cursors in most situations. There is a new OVER clause, but using OVER with aggregate functions and ORDER BY is still not supported. Still the addition of the OVER clause has opened the door of resolving many aggregation-related problems strictly with set-based queries in future versions of SQL Server.
In addition, the new APPLY operator allows application of a UDF to every single row of a result set, in a JOIN-like fashion.
Finally, the advent of CLR integration now allows for development of custom solutions for data aggregation that was not available before. For example, it is now possible to develop a custom UDF that accepts as argument a varchar column of a table and does string concatenation on its contents, much like the standard SUM function does with numeric columns. Such a solution would eliminate the need of developing a loop construct for the same purpose. (I'll admit here though that I have not tested whether this solution is faster than a cursor-based solution - that would be an interesting test...)
For a good discussion of the ins and outs of using cursors and performance considerations versus other looping constructs and set-based solutions, see Chapter 3 of INSIDE MICROSOFT SQL SERVER 2005 - T-SQL PROGRAMMING by Itzik Ben-Gan, Solid Quality Learning.
Also see Chapter 6 of INSIDE MICROSOFT SQL SERVER 2005 - T-SQL QUERYING by Itzik Ben-Gan, Solid Quality Learning, where the OVER clause is discussed in detail.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 31, 2008 at 7:03 pm
Buxton69 (3/31/2008)
Could you point me toward a good resource for examples of code without using cursors, I have some examples where you load a select statement into a temp table and then do a while loop but would like to see other examples.
Why don't you post one of those and let's have at it 😀 Be sure to read the URL in my signature line before you do...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2008 at 7:10 pm
Buxton69 (3/31/2008)
Could you point me toward a good resource for examples of code without using cursors, I have some examples where you load a select statement into a temp table and then do a while loop but would like to see other examples.Thanks
Buxton69
These very forums. In case you hadn't noticed, posting any Cursor based SQL code here is like dropping a live cow into a tank full of hungry piranha. We all race to be the first one to strip that sucker right down to the bone. 😉
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2008 at 8:00 pm
Buxton69 (3/31/2008)
Could you point me toward a good resource for examples of code without using cursors, I have some examples where you load a select statement into a temp table and then do a while loop but would like to see other examples.
I'm with Barry on this one... to use a quote from a movie that's been remade several times... "FEED ME! I'M HUNGRY!" --Little Shop of Horrors
If you want, let's do this in reverse. Right after the classic "Hello World" example, many instructors teach how to make variables and count to 10 using various methods. Lets expand upon that very example... Buxton, how would YOU count from 1 to a million in both SQL Server 2000 and 2005? (You other Pirana's give 'em a change to answer, eh?) 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 12:46 am
Jeff Moden (3/31/2008)
Buxton69 (3/31/2008)
Could you point me toward a good resource for examples of code without using cursors, I have some examples where you load a select statement into a temp table and then do a while loop but would like to see other examples.I'm with Barry on this one... to use a quote from a movie that's been remade several times... "FEED ME! I'M HUNGRY!" --Little Shop of Horrors
If you want, let's do this in reverse. Right after the classic "Hello World" example, many instructors teach how to make variables and count to 10 using various methods. Lets expand upon that very example... Buxton, how would YOU count from 1 to a million in both SQL Server 2000 and 2005? (You other Pirana's give 'em a change to answer, eh?) 😉
Let's have a more "real world" example - partly because I'm curious about the new SQL Server 2005 functionality to see how this could be done.
Say I want to run an ALTER INDEX ALL ON table_name REORGANIZE; command on all tables that are more than 10% fragmented.
I know I can use sys.dm_db_index_physical_stats to get this information instead of using DBCC SHOWCONTIG.
Can we avoid using a cursor in this instance?
Thanks,
April 1, 2008 at 1:22 am
SQLZ (4/1/2008)
Say I want to run anALTER INDEX ALL ON table_name REORGANIZE;command on all tables that are more than 10% fragmented.I know I can use sys.dm_db_index_physical_stats to get this information instead of using DBCC SHOWCONTIG.
No, you have to use a cursor/loop (of some kind). Executing DDL's/DML's across a restricted range of SQL objects is one of those cases (DB management/operations tasks) that almost always requires Cursors. On the other hand, queries across multiple system objects can almost always avoid them in SQL2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply