May 31, 2015 at 9:16 pm
Comments posted to this topic are about the item A Case FOR Cursors...
June 1, 2015 at 12:42 am
I am glad some one had the guts to say that Cursor is not a dirty word and is already used by many objects within SQL Server libraries.
The policy of rubbishing a certain command or logic is not new nor specific to SQL Server. In assembly language programming, in the 80's, a number of developers used to suggest to avoid using JR (Jump Relative) and in VB and C programming languages they used to say don't use GO TO.
All of the above in certain circumstances become unavoidable and that is why they remain part of these languages.
June 1, 2015 at 12:55 am
The fact that Microsoft uses cursors in many sp and functions is only because that codes have been written in earlier version of SQL, starting from 4.2 in '90.
So, except some rare case, there's no reason to use cursors!
June 1, 2015 at 2:12 am
sanjarani (6/1/2015)and in VB and C programming languages they used to say don't use GO TO.
All of the above in certain circumstances become unavoidable and that is why they remain part of these languages.
I have never, ever, encountered a situation in either VB or C where the only option available was to use GOTO. Where have you found no alternative to it?
June 1, 2015 at 2:12 am
Added a cursor just the other week when a recursive CTE used in a cross apply was causing a query to take over the .NET default 30 second timeout - 34-40 seconds in some cases.
I removed the cross apply and used the same recursive CTE in a cursor on the same data set and boom it took only 2-3 seconds to populate a temp table that I inner joined to the original query.
June 1, 2015 at 2:13 am
I have never read the statement "do not use cursors" in any other way than interpreting it as "do not use row-by-row operations, use set operations instead".
So I do not quite see the point of this article. Most SQL developers out there already interpret it that way, and use it that way, because obviously that is what is wrong with it. It isn't really anything new to interpret it in this fashion. Cursors are but one incarnation of the row-by-row mindset.
And in some rare cases one does have to use row-by-row operations. It's not "forbidden". One should remind oneself why the negative connotation of row-by-row operations came about - it is that they are inefficient in dealing with large amounts of rows.
A C# or VB programmer starting to learn using SQL simply has to learn to shift his mindset towards set based operations and realize that in SQL databases these are more efficient. This is what this is all about.
June 1, 2015 at 5:49 am
I appreciate the article. I have seen great lengths that people go to, to avoid the dreaded Cursor (including changing all cursors to arbitrary WHILE loops).
Similarly I don't see the point in the "GOTO" furor that still remains. Most of your procedural control language (i.e. C style syntax) is not in a single method anymore. Introduction of classes/Objects, multi-file, and multi-assembly projects have made GOTO only useful in limited circumstances (VB6 error handling anyone?). There are cases where GOTO is needed (think embedded programming on limited hardware that a method frame take more memory than is available), but those are very few and far between. The issue that "GOTO considered Harmful" was addressing has largely evaporated with modern languages and needs.
CURSORS have their place... it just shouldn't be the first choice... or the second. It should be the last choice, within reason.
June 1, 2015 at 6:09 am
Although set operations are usually a better option when possible, there are some cases where row-by-row operations are necessary. Examples may be looping through a table and performing an external operation for each row like sending an e-mail, or performing some type of batch job on several databases by looping through a set of databases and running a procedure inside of each one. As long as the set that a cursor is looping through is relatively small, there is little impact on resources. Also, when row-by-row operations are necessary, cursors are usually a better option than WHILE loops.
June 1, 2015 at 6:14 am
Gasp :w00t: - an article that just does not tell to stop thinking and just doing as a script???
What shall the world come to when we get authors to make us think? T'is's bad practice, y'know? We could end up with "artificial intelligence".
June 1, 2015 at 6:18 am
I partially concur with your assessment; sometimes cursors/while-loops are unavoidable when a given task cannot be achieved with a set operation. However, in my support of over 800 databases, I'm here to tell you they, in fact, are evil. The overwhelming majority of cursor usage in our vendor-built databases could be avoided with the use of set-based operations. In some instances, I've had to isolate databases on their own servers because of the I/O overhead they draw. In one instance, a vendor's product had over 200 cursors, 64 of which had nested cursors, 15 of which were 5 deep. Talk about time-out issues! When our users couldn't accomplish their work, I had to contact the vendor and educate them on set-based theory. They were on the hook for support, so I got them to at least fix the 15 stored procedures that had 5-deep nested cursors to use set-based processing. The end result was over a 3000% increase in performance. Let's just say the vendor (who shall remain nameless) had an "Aha" moment.
June 1, 2015 at 6:31 am
While I agree that cursors can (and should) be avoided most of the time, it is not ideal to utilize set-based operations in all cases. Especially in very-large OLTP databases where it might be necessary to minimize transaction size as much as possible. Very-large transactions in highly-concurrent environments can cause a lot of undesired events to happen (increased wait times, transactional replication latency, log file growths, etc), and often times the best remedy is to minimize transaction sizes to prevent these events from occurring in the first place.
There is definitely a legitimate use case for Cursors. Ironically, in my experience it seems that the case for cursors grows as the size of data & concurrency increases.
June 1, 2015 at 6:57 am
I have never read the statement "do not use cursors" in any other way than interpreting it as "do not use row-by-row operations, use set operations instead".
Agreed. Some of the system procedures probably can't avoid the row by row operation either. I have never needed an actual cursor, and have replaced many I've come across with a more efficent set-based queries. These cursors were very inefficient and caused bottlenecks. I do use the WHILE loop when populating my datawarehouse calendar table, an operation done only once every few years and only taking a few seconds in any case.
As a former programmer, I can appreciate that some programmer DBAs might use the cursor as a first result because they have not developed a set-based mindset yet. In that case, the first priority is to do something that works. The second priority is to continue to develop the skills so unnecessary cursors can be replaced.
While a programmer, I never heard that GOTO was bad. Why is that so bad?
June 1, 2015 at 7:00 am
Sometimes we use CURSORS in our order processing environment to move data between systems and changing statuses on each record processed. The main reason for using a cursor is that one record MUST NOT stop other records from being processed. These data movement operations can easily be done as set operations, but that requirement cannot be ignored. So, I really wish that SQL Server would have a way to determine which record is the culprit for blowing up a set operation, log it and move on with processing. This is really the only scenario in our environment where I allow cursors to be used. Tracking the run time on these processes is a must because, as mentioned before, they do not scale very well.
An alternative to this scenario that I can see is using the service broker. But the trade off in complexity (coding, monitoring, replication of SB services, etc) is not worth it at this time for us.
June 1, 2015 at 7:05 am
Sometimes we use CURSORS in our order processing environment to move data between systems and changing statuses on each record processed. The main reason for using a cursor is that one record MUST NOT stop other records from being processed.
Depending on the complexity of what you're doing, you could consider using set based queries to test the data and then flag the rows. I have the same situation with the OLAP download. Single records can't stop the rest going forward. Each record has a flag that is default No, and only updated to Yes when all the checks are based.
June 1, 2015 at 7:16 am
RonKyle (6/1/2015)
Sometimes we use CURSORS in our order processing environment to move data between systems and changing statuses on each record processed. The main reason for using a cursor is that one record MUST NOT stop other records from being processed.
Depending on the complexity of what you're doing, you could consider using set based queries to test the data and then flag the rows. I have the same situation with the OLAP download. Single records can't stop the rest going forward. Each record has a flag that is default No, and only updated to Yes when all the checks are based.
That's a good point. Unfortunately, testing for valid data (ranges, types and all other variations) on most columns is not worth maintaining that type of code and running it when there could be a bad record once every 3 months. Better to just fail, alert and act on it while other records are unaffected. Would be awesome if set based operations could log the failing records somewhere and throw it out of the set and continue processing. That would be a great feature. 😀
Viewing 15 posts - 1 through 15 (of 215 total)
You must be logged in to reply to this topic. Login to reply