Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Optimizing Cursor Performance Expand / Collapse
Author
Message
Posted Tuesday, January 18, 2005 1:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 28, 2005 7:56 AM
Points: 27, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kThaker/optimizingcursorperformance.asp
Post #156205
Posted Monday, January 31, 2005 2:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 4, 2006 3:35 AM
Points: 31, Visits: 1
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
Post #158469
Posted Monday, January 31, 2005 9:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:16 AM
Points: 1,035, Visits: 410

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



*****************/
Post #158558
Posted Monday, January 31, 2005 11:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:47 AM
Points: 932, Visits: 364
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
Post #158583
Posted Monday, January 31, 2005 2:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 4, 2006 3:35 AM
Points: 31, Visits: 1

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
Post #158613
Posted Monday, January 31, 2005 2:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 6, 2007 12:57 AM
Points: 53, Visits: 1
is it just me, or is the article really center aligned? It makes it hard to read I think.


Post #158619
Posted Monday, January 31, 2005 2:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:16 AM
Points: 1,035, Visits: 410

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



*****************/
Post #158621
Posted Monday, January 31, 2005 3:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 4, 2006 3:35 AM
Points: 31, Visits: 1

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
Post #158643
Posted Monday, January 31, 2005 4:01 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 12, 2007 6:32 AM
Points: 62, Visits: 3

Nice article.  I'm glad the forum already beat me to asking the question about Oracle cursors.

Post #158652
Posted Monday, January 31, 2005 4:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:16 AM
Points: 1,035, Visits: 410
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



*****************/
Post #158657
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse