Optimizing Cursor Performance
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kThaker/optimizingcursorperformance.asp
-
Nice article, I must admit I was drawn in by the description on the front page which reads <quote> Since SQL Server 2000 is optimized to run with set based solutions, unlike Oracle, cursors severely degrade performance</quote>. The article doesn't in fact compare sql to Oracle, but it isn't true either that Oracle is not optimized for set based solutions, or that inappropriate use of cursors doesn't degrade performance. The points that the article makes about the benefits of avoiding cursors whereever possible apply across both platforms. It is true that Oracle code performs better than SQL for cursor based solutions, but almost always a set based solution will perform better still.
Niall Litchfield
Senior DBA
http://www.niall.litchfield.dial.pipex.com -
Unlike Oracle?!?!?! The idea that Oracle runs perfectly fine with cursors is a complete fiction! What is true is that Oracle (the company) doesn't make as big a deal of it as Microsoft does, and consequently almost no Oracle books even mention the fact that cursors are performance killers. Rather than being a point in Oracle's favor, I view this as tantamount to false advertising. But unfortunately, this false idea is pervasive among developers and DBA's alike.
I have worked extensively with Oracle, but since I started in SQL Server, I had a strong anti-cursor mindset pounded into me from the start (thank goodness). When I started working with Oracle and saw the extensive use of PL/SQL (which is all cursor-based as opposed to SQL) I questioned some of the developers and my fellow DBA's. At first I believed the line that "Oracle does fine with cursors." But after several months of familiarization, I decided to re-write some of the PL/SQL code to use JOINS instead of cursors etc... What I saw confirmed my suspicions. The same logic using SQL was many times faster than PL/SQL; in many cases it was an order of magnatude faster!
I couldn't believe that an entire department of developers and DBA's just swallowed such an unlikely line of BS. There is nothing magical about Oracle, it handles sets much more efficiently than row-at-a-time operations, just like SQL Server (and DB2 for that matter). Conversely, there is nothing "wrong" in SQL Server as compared with Oracle.
/*****************
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
*****************/
-
I would disagree with the contention that STATIC cursors should be avoided. Obviously, there are instances where this is true, and yes, this does cause the result set to be written to tempdb.
However, the author does not go on to explain what happens (or at least what my experience seems to indicate happens) with a dynamic cursor. Here's an interesting experiment I (inadvertently) tried once. I ran a dynamic cursor update loop that would update 4 records out of 10 million, and another that would update 10,000. My results? The two operations took almost the same amount of time to complete. Switching to local static cursors, performance of the 4 row update improved dramatically.
However, as is almost always the case, there is a significant time vs. space issue here. If you're tight on disk space, and the speed of the update is not an issue, then regular cursors can be your best bet (although, alternately, you might be able to work with a table variable, if you've got the memory to spare).
If you are selecting data based on unique key values, especially if those values are primary keys, and you have the space in tempdb to hold the cursor result set, use a local static cursor to find the keys.
R David Francis -
I'm not entirely sure what books you refer to. Certainly both the VP of Server technologies and the docs have suggested that one would naturally prefer set based methods to cursor solutions for at least 5 years eg http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:418619433157 . The same topic has frequently occurred on usenet with the same advice. If it is pervasive amongst the Oracle community there is little published evidence of this. False advertising is a strong claim, I suspect here it is over done.
What is true is
1) that the infinite loop that is described in the article could not occur in Oracle because of read consistency. This would be true even for an IOT.
2) avoiding sets for procedural code is a sure fire scalability and performance killer - its a shame that J2EE and .Net developers often don't get this.
Niall Litchfield
Senior DBA
http://www.niall.litchfield.dial.pipex.com -
is it just me, or is the article really center aligned? It makes it hard to read I think.
-
Certainly the Sybex OCP Study Guides for 8i had no reference to the performance implications of cursors. I know, because I searched through them... I also grabbed every Oracle reference book owned by anyone in the entire department, most of which came from Oracle Press, and which included "tuning and diagnostic" books; while all of them dealt with HOW to code cursors, NONE of them so much as hinted that there were performance implications to doing so. Further, every one of the dozen or so developers and DBA's that I discussed the subject with, were under the "impression" that Oracle was somehow different, and thus immune from the performance problems that SQL Server labored under. I was assured time and again that I should quit barking up that tree. It wasn't until I devised several tests and proved my point that anyone would listen.
The problem, like you point out, is that those who are used to procedural (or OO) code are uncomfortable with set-based operations and they tend to see cursors as very familiar and "easy."
Granted, my experience is subjective and not in any way a comprehensive study of the subject, and I have run into Oracle DBA's and developers who know better. However, the vast majority of SQL Server DBA's/developers are aware of the drawbacks of using cursors because, among others, Microsoft is very up-front about their potential problems. At least a majority of people I have discussed the issue with (both SQL Server and Oracle folks) labor under the misconception that somehow Oracle "is optimized for cursors" or some such claptrap.
Don't misunderstand, I'm not bagging Oracle, I've worked with it for several years now and appreciate it's strengths, but I'm also not blind to its weaknesses...
/*****************
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
*****************/
-
No worries, I didn't think you were bagging Oracle. It was more a marketing thing for me. neither the sybex books - nor, more particularly, the Oracle Press books have anything to do with Oracle corp. The latter is a marketing agreement between Oracle and the publisher - the publisher, not Oracle, is responsible for the technical accuracy (or rather not) of the books.
<slight marketing bit>
Incidentally I did have the pleasure of tech reviewing Mark William's book on programming Oracle with .Net technologies (http://www.apress.com/book/bookDisplay.html?bID=378 
. I think that the book was good - but 2-3 days to tech review whole chapters (which is apparently longer than the industry standard) makes it somewhat unsurprising that the quality of book is often very variable</marketing>
Niall Litchfield
Senior DBA
http://www.niall.litchfield.dial.pipex.com -
Nice article. I'm glad the forum already beat me to asking the question about Oracle cursors.
-
I should have also included the on-line Oracle documentation... I realize that Oracle Press is not exactly the same thing as official Oracle documentation, however there is an implied authority there, much like the Microsoft Press books.
/*****************
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
*****************/
-
I realize that there is an implied authority there. I hate it, in fact I probably would use the words false advertising 'officially authorized Oracle Press' does not suggest to me that it is the name Oracle Press that has been authorized rather than the books content.
Niall Litchfield
Senior DBA
http://www.niall.litchfield.dial.pipex.com -
I always avoid cursors but they are, unfortunately, necessary sometimes. I did notice a huge difference between SQL7 and SQL2000. SQL7 would just fold in two as soon as you tried to run any cursor. SQL2000 gives acceptable performance as long as you don't over-use them.
I too, thought it was strange when I read that Oracle was supposed to be better with cursors in genral than MSSQL Server. Definitely not from my experience.
-
WOW... I finally got around to checking some of my cursor code to make sure I was using those declaration flags he mentioned and sure enough I wasn't.
My sprocs executed reasonably fast anyway because I always prefetched data into a temp table and looped around that dataset instead of the production tables and I usually ran the sprocs late at night anyway so changing data never was a problem.
But the addition of the LOCAL and FAST_FORWARD enhanced performance dramatically (in some cases over 100 times faster). This makes a big difference to the more 'on demand' type of sprocs and sprocs with cursors call other sprocs with cursors (nested cursors! ick, I know). I am very pleased with all of my cursor executions now (but I still wouldn't use cursors in lieu of grabbing a data and processing it externally when I could)
-
This article, http://www.sqlservercentral.com/columnists/kThaker/optimizingcursorperformance.asp
supposedly by Kalpesh Thaker, appears to be lifted almost word-for-word from Ken Henderson's book The Guru's Guide to SQL Server Architecture and Internals, published in 2003.
-
I have several of Henderson's books but I don't have the one you refer to so I can't verify your statement myself. Just a few thoughts though...
As a sometime autor myself, one of the things I have to struggle with is the fact that almost everything that needs to be written about SQL Server already has been written in some form or another. Between Books Online and the myriad of third party books including excellent titles by people like Kalen Delaney, Ken Henderson, Joe Celko, Chris Date etc... I can hardly think of any areas that are not pretty well covered. Almost anything I can say is merely a rephrasing of what has already been said.
I made a concious decision to wade in (at least up to my knees) and attempt to bring something of value to the community. I applaud others that have made the same decision even if our efforts might be somewhat deficient owing to the fact that I am not a professional writer and do not have the benefit of editors and proofreaders etc...
That said, there is certainly a world of difference between using ideas that have been expressed elsewhere and expressing them in your own language (hopefully recombining them and adding clarity and/or emphasis) and ripping off the work of another person. Ideas and concepts can't be patented or copyrighted, but the process or syntax of expressing those ideas can.
Unless the article is truly a word for word copy (or nearly so) of Henderson's work, Kalpesh Thaker ought to be given the benefit of the doubt.
/*****************
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
*****************/
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply