January 20, 2006 at 1:33 pm
>>I don't think you understood... the whole idea is to get rid >>of cursors and other RBAR methods in SQL Server.
My point is that the idea of "getting" rid of cursors/justifying their non-existence dates back to when there weren't any cursors in the SQL Server world to justify. And the code that started this thread is nothing new but dates back to when cursors did not exist in the SQL Server world.
Engineers should be making use of all available tools when applicable. And running a cursor, particularly against a temp table, specifically when there is no logical way to perform a set operation, is a valid application of an available tool.
After we all get done discussing this topic, lets move onto something even more exciting, like 3rd normal form!!!
January 20, 2006 at 6:02 pm
Ummmm.... wanna tell me what version of SQL Server there was that didn't have cursors?
--Jeff Moden
January 20, 2006 at 6:19 pm
You hit the nail on the head: "And running a cursor ... specifically when there is no logical way to perform a set operation, is a valid application of an available tool."
Very, very, very rarely is a cursor absolutely necessary. There is almost always a way to conquer T-SQL tasks using T-SQL, sans cursors. Over-reliance on cursors is a fallback position for many people who come from backgrounds in flat-file processing, and want to use SQL server as a flat-file parsing tool.
If all you want to do is process data flat-file style, there are much better tools available than SQL Server cursors.
January 21, 2006 at 3:29 pm
Great discussion! As usual there are many ways to solve a problem and each of us has to determine based on our own experience how best to analyze and address them as they arise. Cursors are just one tool in our bag and though I would never use them as a first choice I guess I wouldn’t throw them out entirely …but then you should see my garage.
As others have pointed out Cursors are notoriously inefficient in SQL Server. As a general rule you want to avoid processes of long durations not only because they consume more resources but also because the longer a process takes the more potential that exists for blocking and other problem conditions to arise. I offer the following true case history as an example:
In the late nineties I was hired by a national distribution and warehousing company to assist them in a project to upgrade their SQL Servers from 6.5 to 7.0. The critical application was an order processing system. Orders were entered on AS 400’s and consolidated on an HP 3000. They were then imported via flat file batches several times per day into a SQL Server where business rules were applied and orders were replicated to the various warehouses around the country for processing and shipping. Shipping status was updated and files then replicated back to headquarters.
My first thoughts were that while the upgrade would no doubt result in some improvement in performance there were most likely other factors related to the code and/or architecture that were likely at the root of their problems. I told them that I would certainly assist in the upgrade but would also like to analyze the code to see if there were any obvious issues that could be causing their problems. Convincing them to modify the code was going to be a challenge since it was written by the current IT manager whom I later learned had been a cobol programmer earlier in his career.
When I finally got down to business and began looking at the code I discovered cursors everywhere. …cursors in the code that applied the business rules of course but also cursors during replication and cursors during the update of shipping status. I knew immediately that the upgrade was not going to meet their expectations but I met with a lot of resistance from management.
When the upgrade was complete processing times did improve somewhat but we were still experiencing significant blocking at various times especially during replication and status updates. I finally convinced them to let me rewrite the process. Following analysis of the code I restructured the staging tables and was able to break the code down into 16 processes that applied the various business rules and pre-processed the order records prior to replication. I also modified the replication process to eliminate most of the code including all of the cursors by moving the logic forward into the pre-processing code.
When the re-write was complete we ran a series of tests demonstrating that the new processes performed the same function as the old and produced identical results. A typical batch of 10,000 orders that took just under 2 hours to process from bcp in to replication complete took approximately 2 ½ min. using the new code. This improvement proved to be extremely important. The old process meant that orders could only be processed every two hours and that a maximum of 240,000 records could be processed in a 24 hour period. While this was not an issue most of the time it did have a negative impact during the peak summer months when retailers were placing their orders for the coming Christmas season. Downtime in the year prior to the upgrade and rewrite had been in the hundreds of hours (at an estimated cost of $26,000/hr). Downtime in the year following the upgrade was 0 hours.
I only wish I had negotiated a contingency contract instead of an hourly rate.
-ron cicotte
Ron Cicotte
ron.cicotte@gmail.com
Data Transformations, LLC
January 22, 2006 at 9:21 pm
I'm getting into my "Way back machine" here, but I think support for cursors was introduced in 6.5.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 21, 2006 at 4:02 am
Cursors existed in 4.2 (Now Im showing my age) and as far as I could remember could achieve results we could not get any other way. SQL server was extensively re-writen for 7.0 which introduced much better solutions but left cursors as an option and they remain today, we have had regular examples of cursors being replaced with set based operations resulting in run time going from a number of minutes to just a few secs...
July 21, 2006 at 5:57 am
Thanks, DC and Ieea,
I always thought cursors were a part of SQL Server... didn't know they were actually added as a "feature" at some point. Must'a been to draw the folks that couldn't get over the "read a record, write a record" paradigm (or is it 4 nickels :hehe
in the ISAM world.
--Jeff Moden
July 21, 2006 at 6:08 am
I think that in your article the biggest difference in speed between cursor and while loop exists because of use of temporary table. Have you tried to use cursor instead of while loop in your second code example? Could you do it and comment results?
I know that for same kind of select statement especially when we join multiple tables to get result, temporary table can improve performance because the number of records used in query can drop down dramatically. And maybe in your case (you grab data for cursor from multiple tables) you can speed up process without replacing cursor by while statement.
I do not know what are you doing in stored procedure which you called inside cursor/loop but sometime is good to change the logic and check if it is not possible to replace cursor-stored procedure call to just stored procedure with select statement inside it which will do work without cursor at all.
Thanks
July 21, 2006 at 7:08 am
I fought against the inclusion of cursors in the original SQL spec, but COBOL programmers of the late 70's had great difficulty wrapping their mind around a technique that did not allow them to touch every row.
As Jeff Garbus puts it, "Cursors are the last refuge of those who refuse to accept set processing."
Hierarchical structures are the only situaions that I have seen in 30 years of working with RDBMS. Suffice it to say that if you are using the relational tool to solve hierarchical problems you're using the wrong tool. Finally, XML has come to the rescue.
July 21, 2006 at 7:16 am
The original code for SQL Server was developed by Sybase. Microsoft, Ashton-Tate and Sybase collaborated on the first version of SQL Server (4.2) built for OS2 in the late 1980's (yes, I'm old enough to remember it). At v4.2 Sybase and Sql Server were virtually identical in syntax and functionality. Sybase has since renamed their product Adaptive Server Enterprise.
So SQL Server has always had cursors because of its origins. But set based queries are orders of magnitude more efficient. I once re-wrote an entire order processing system originally written using cursors* to use set based queries. Prior to the re-write it would take approximately 1.5 - 2 hours to process 10,000 orders. Post re-write the same process took less than 2 minutes.
-ron
*Note: written by a former cobol programmer
Ron Cicotte
ron.cicotte@gmail.com
Data Transformations, LLC
July 21, 2006 at 7:26 am
Does anyone have a link to some examples of scripts using cursors and the same scripts without the cursors, I used to have some good examples but can't find them now 🙁
July 21, 2006 at 7:50 am
I bet this is the last time anyone brings up the subject of cursors!!
July 21, 2006 at 7:53 am
bet its not 😉
July 21, 2006 at 8:03 am
I tried this solution. It gives a reduction with small set of rows. From 10000 rows it is just as fast. I tried it with a return set of 6 million. The procedure still run after a weekend.
So for small sets it works. But not as a general solution.
July 21, 2006 at 10:26 am
Since the thread has come alive again, I resubmit my last post, to get the opinions of the community.
On a different note, how do you approach eliminating a cursor for "batch" inserting correlated items and the parent item uses an identity as PK (for example, inserting a batch of orders and the corresponding orderItems)?
I have two approaches, but neither is very ellegant:
Another approach is with the help of triggers, but I'm not very fond of using triggers
Viewing 15 posts - 61 through 75 (of 296 total)
You must be logged in to reply to this topic. Login to reply