Use of Cursors
-
Can someone tell me or direct me to a good article on why one should not use cursors in stored procedures?
-
I wouldn't say you should never use cursors, just be aware of their pitfalls and avoid them when more efficient methods are available.
Check the following topic in Books Online,
"Optimizing Application Performance Using Efficient Data Retrieval"
These links might be helpful also,
http://www.sql-server-performance.com/cursors.asp
http://www.sql-server-performance.com/dp_no_cursors.asp
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 12/15/2003 5:03:38 PM
Edited by - phillcart on 12/15/2003 5:04:20 PM
--------------------
Colt 45 - the original point and click interface
-
I think that it is worth mentioning that you should not use cursors in triggers. The reason is that you want triggers to complete their actions as quickly as possible or you're looking at a serious bottleneck in performance.
-
In short, SQL Server works best with sets of data rather than at an individual record level so in most cases eliminating cursors will yield a performance boost.
Cursors are there for a reason. There are situations where there is no alternative than to use the cursor.
One of my colleagues has come from an ORACLE shop and he says that ORACLE is the complete opposite i.e. works better with cursors.
========================
He was not wholly unware of the potential lack of insignificance.
-
quote:
Can someone tell me or direct me to a good article on why one should not use cursors in stored procedures?
SQL Server is based on the relational model (or rather it's based on SQL, which is based on the relational model but not a very good implementation of it). The relational model is essentially the application of predicate logic and set theory applied to data management. Although Codd did 'allow' low-level language access to data (i.e. cursors), they cannot benefit from the relational model in the same way that a set-based query does and I would generally call them a workaround for either poor design or solving a problem the wrong way. Of course, since SQL Server is not a true RDBMS it is there may or may not be situations where one could need them, but I would still say it is wrong to use them even if you must do so at times.
--
Chris Hedgate @ Extralives (http://www.extralives.com/)
Co-author of Best of SQL Server Central 2002 (http://qa.sqlservercentral.com/bestof/)
Articles: http://qa.sqlservercentral.com/columnists/chedgate/
Edited by - chrhedga on 12/16/2003 03:33:09 AM
-
quote:
SQL Server is based on the relational model ....but not a very good implementation of it.Of course, since SQL Server is not a true RDBMS...
Could you expand on this?
What is a good example of a good implementation? I suppose this is going to be ORACLE, it sure as hell isn't Sybase!
========================
He was not wholly unware of the potential lack of insignificance.
-
Don't mix up SQL and the relational model! That are two different pairs of shoes (can I say so?).
I think SQL Server does quite a good job on implementing SQL standard and I pretty much hope that Chris don't mean Oracle, but rather DB2 or something else.
The main issue with cursor IMHO is that they turn it into row-by-row processing, kind of procedural approach and therefore leading set based theory ad absurdum.
There *might* be situations where a cursor seem the right choice, but if you ask me, I don't know such a situation right now.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I've had situations where I have had to use cursors.
I had a recordset containing the stages in securing a sale for customers.
The complete set of stages was called a chain.
Certain stages marked the beginning of a chain and others were defined as ending the chain. A chain could have any number of stages up until a stage indicating the end of chain occurred.
The question was asked,
- How many stages are there on average from lead generation until the sale?
- How many chains does the customer go through.
I did this by cycling through the sorted recordset. Obviously the change of customerid marked the beginning of a new chain.
I recorded the chain number and chain step number into the recordset.
The only way I could think of to do this without cursors involved multiple complex queries.
The cursor equivalent was straight forward and simple.
I was tempted to do this in an external application rather than as a pure SQL approach but in the end the pressures of time and reluctance to give a SQL guy decent tools (not classed as a developer you see) ruled this out.
========================
He was not wholly unware of the potential lack of insignificance.
-
That's what I really hated when I was professionally developing.
Commonly you don't have the time to implement an efficient solution, but rather an effective one.
It's was one of the reasons why I quit my job. Now I have much more time to develop what I want to (don't know if my solution got any better from this, though)
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
quote:
SQL Server is based on the relational model ....but not a very good implementation of it.Of course, since SQL Server is not a true RDBMS...
Could you expand on this?
What is a good example of a good implementation? I suppose this is going to be ORACLE, it sure as hell isn't Sybase!
I think you quoted me rather bad there. Or maybe I wrote bad... 🙂
What I meant was this: SQL Server is based on SQL, which is based on the relational model but a rather bad implementation of it. There is no good implementation of the relational model, save maybe Alphora Dataphor, since most other so-called RDBMSs are based on sql. Anything based on sql can not be a good implementation of the relational model. I guess terms of more bad implementation than others are not a good way of talking, but as far as Oracle goes I would say it is definately not better the SQL Server. IMHO Oracle is more tightly mixed between logical and physical layer than what SQL Server or DB2 are, but neither are still a good implementation of the relational model.
That said, as you see, I am still a user of SQL Server (and other dbmss) and like it very much, but it is still important to be aware of how it could be better and to try and do your part for working for better solutions. Therefore I would recommend tking a look at Alphora, they have a really nice product.
Finally, regarding cursors (if I didn't make that point clear earlier), I agree with Frank. In SQL Server there might be situations where a cursor is needed, but I don't know one and should I encounter one I would probably take a step back and review if anything else should be done to avoid the situation. If absolutely necessary I would probably leave procedural work to the application.
--
Chris Hedgate @ Extralives (http://www.extralives.com/)
Co-author of Best of SQL Server Central 2002 (http://qa.sqlservercentral.com/bestof/)
Articles: http://qa.sqlservercentral.com/columnists/chedgate/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply