February 15, 2011 at 12:44 pm
I'm studying for the Data Access exam... so I was just wondering if there were ever a case when cursors are necessary. I know that one should test a cursorless example against a set based one and see which performs better... but does anyone have any pointers on where they're a necessary evil? About the only thing I can come up with is when you need to call a non-deterministic function and loop through the records for some reason (e-mail?).
Sorry for the vague question - wasn't sure how else to explain it.
Thanks!
Pieter
February 15, 2011 at 12:57 pm
well i use cursors alot for management reports that go across 50 servers.
so that you create the dyanmic sql for the server name and a cursor thru the servers
February 15, 2011 at 12:57 pm
supposed you have a stored procedure (sp_helptext for example), and you want to call it for every procedure/function or view in the database;
you'd need to either use a cursor, or generate all the commands in a big varchar max and execute it ; but regardless, because of the procedure's design, you are handcuffed into using some sort of loop or cursor.
now, there might be a different WAY to do the same thing (in this case reading the definition from sys.sql_modules), but there might be a business step that follows the same criteria...expecting a value or a key, and it has to be performed multiple times.
but in that example, there's no way to call the proc multiple times without the looping construct, right?
my rule of thumb is that if i'm building stuff based on metadata, cursors are usually ok.
Lowell
February 15, 2011 at 1:00 pm
your email example is another good one; if you need to send an individual email based on each row in a given select, there's no other alternative than a loop or cursor.
Lowell
February 15, 2011 at 2:08 pm
Lowell (2/15/2011)
your email example is another good one; if you need to send an individual email based on each row in a given select, there's no other alternative than a loop or cursor.
Well, actually there are other alternatives - but in my opinion those alternatives are just as hard to manage and maintain as a cursor, and in some cases actually harder to maintain.
As was stated before, you could always generate the code to be executed and then execute the batch using sp_executeSQL. This will avoid the cursor - but won't really make much of a difference in performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 15, 2011 at 2:11 pm
Any place where you need to dynamically run against objects in the database or server, or call a stored procedure over and over vs a dataset, when the results aren't really a dataset themselves. Multi-server or multi-database scripts for maintenance tasks are my most common uses of them.
Also, for things like running totals, you end up using one in some form or another, or you end up building "triangular joins" and killing performance even worse than a cursor.
- 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
February 15, 2011 at 2:25 pm
GSquared (2/15/2011)
Any place where you need to dynamically run against objects in the database or server, or call a stored procedure over and over vs a dataset, when the results aren't really a dataset themselves. Multi-server or multi-database scripts for maintenance tasks are my most common uses of them.Also, for things like running totals, you end up using one in some form or another, or you end up building "triangular joins" and killing performance even worse than a cursor.
Do you consider the "quirky update method" for running totals as some sort of a cursor, too? Just wondering...
I second Jeffreys statement regarding email alternatives. Without having actually tested it think the statements could be prepared as dynamic SQL - which is nothing but creating a batch of similar SQL statements that might be considered as a sort of loop, too.
(However, I'm confident it can be done using SSIS, but that's a different story, so in this context I wouldn't throw SSIS into the dsicussion as a valid option ...).
February 16, 2011 at 7:17 am
LutzM (2/15/2011)
GSquared (2/15/2011)
Any place where you need to dynamically run against objects in the database or server, or call a stored procedure over and over vs a dataset, when the results aren't really a dataset themselves. Multi-server or multi-database scripts for maintenance tasks are my most common uses of them.Also, for things like running totals, you end up using one in some form or another, or you end up building "triangular joins" and killing performance even worse than a cursor.
Do you consider the "quirky update method" for running totals as some sort of a cursor, too? Just wondering...
I second Jeffreys statement regarding email alternatives. Without having actually tested it think the statements could be prepared as dynamic SQL - which is nothing but creating a batch of similar SQL statements that might be considered as a sort of loop, too.
(However, I'm confident it can be done using SSIS, but that's a different story, so in this context I wouldn't throw SSIS into the dsicussion as a valid option ...).
"Quirky update" is definitely a cursor. It's just not a declared one. It's all about counting on the engine to process row-by-row in some hoped-for sequence. Same as any explicit vs implicit coding.
- 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
February 17, 2011 at 3:12 am
I had a recent example where a developer had created a stats-related Stored Procedure that worked through a decently formed set of joins across tables and Views, applying an update to some of the rows - he is a good developer and this was good set-based thinking. The base data was quite large (13 million rows) but it was updating only a few hundred or a thousand rows. It took 10 mins to run, occupying almost 4 GB of tempdb each time, and was executing every 15 mins(!).
It was probably possible to improve and optimise the SP. I suspect reducing the columns retrieved, accessing only the necessary View data more explicitly, and looking at ordering the selection criteria would have helped. But I had no time, and as a temporary fix (the job will shortly be re-implemented using a different system) the developer cut the run time to between 4 and 30 secs (depending on time of day and load) by using a cursor instead.
I think the cursor is efficient here because it is highly selective about the rows it will update, and can scan for these individual rows faster than the large joined dataset can be built. I don't suggest this proves anything, it just implies that in some circumstances either row-by-row processing may be quicker, or row-by-row thinking may enable a developer to come up with a slicker solution.
In SQL terms this does not fulfil your requirement that there is "no other option" but to use cursors, but if you factor-in server impact and performance and system utilisation as operationally relevant, it might provide an example.
February 17, 2011 at 4:41 am
The only occasion I have deliberately used a cursor solution (other than through laziness!) was for an archiving job.
The server was used 24/7, so there was no ideal time for an archiving job.
I started off with a simple DELETE xxx WHERE xxx which ran for 10 minutes (just the sheer number of rows to be removed and the overhead of merge replication), blocking everybody.
I didn't care how long the archive took, but wanted it to have the absolute minimum impact on other users. In the end, I changed this to retrieve the list of rows to remove and store them in a temporary table, then cursor through the temporary table, deleting one row at a time with a small delay every n rows.
It ran for ages, but completely eliminated the blocking.
February 17, 2011 at 6:14 am
There are numerous places where cursors are acceptable if not preferred, IMNSHO. What we REALLY need is faster-performing cursors from the Microsoft engine team!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 17, 2011 at 9:43 am
Really interesting discussion! (nearly wrote "disscussion" which almost seems appropriate!) thanks everybody for your input and examples.
February 17, 2011 at 9:52 am
TheSQLGuru (2/17/2011)
There are numerous places where cursors are acceptable if not preferred, IMNSHO. What we REALLY need is faster-performing cursors from the Microsoft engine team!!
Use CLR procs/functions for those. CLR engine can make step-by-step processing really fast, if it's done right. Static cursors can be really fast, too, but don't have the flexibility of CLR loops.
- 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
February 17, 2011 at 11:45 am
Any time the order of processing records has to be considered while accumulating multiple values along the way, a cursor can be the easiest code to program and maintain. Quirky updates and triangular joins are just too messy in some cases.
For example a Point Of Sale system where there are mix/match codes for the items being sold. Mix/match codes are for discounts when when the customer buys a certain number of items from some category or categories. Items can be in multiple categories. Customer buys 3 of item A in category A, 2 of item B in category B, 2 of item C also in category A. He now has 5 in category A so a discount is applied to the sale line of '2 of item C'.
I once did this in set based code and it was so ugly that I threw it away and ended up with a cursor to handle it. After all, there aren't that many lines in a normal sales ticket. A typical sales checkout does 1 item every 2 - 5 seconds.
Todd Fifield
February 17, 2011 at 11:55 am
The truth is you don't NEED cursors. I'm confident that any query you want to perform can be done without the use of cursors. I have done performance testing and have always been able to create a cursor-less solution that performs considerably better.
The only feature (problem) that cursors provide is real-time update (interference) with your results. For example, if you are doing updates in your loop that change the results of the cursor, and you are not using a STATIC, READ_ONLY, INSENSITIVE, or FORWARD_ONLY argument, then SQL will reload the cursor before your next fetch. Thus one reason for the huge performance hits you get with them. I have seen this cause an infinite loop situation and unexpected results. Say you are counting the number of rows with a column value greater than 100, but in your loop, you decriment that column value. Some rows may drop out of the initial cursor as you are processing it. But if you were also counting those rows in a variable as you updated them, the count would not match the query at the end of the process.
Maybe you can think of times you'd like to have your queried data change out from under you as you are looping through it, but for me this is more of a headach than a feature. So if I ever use a cursor, I define it as a FORWARD_ONLY READ_ONLY STATIC cursor.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply