| Author |
Topic |
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
 Posted - 2006-01-13 : 11:16:56
|
| I have seen in many replies, to avoid cursors.Is the cursor concept is worthless or obsolete?if notWhen it is useful? |
|
|
szgldt
Starting Member
10 Posts |
Posted - 2006-01-13 : 11:39:51
|
| In my experience it is best to avoid cursors unless there is not other alternative available. "Cursors force the database engine to repeatedly fetch rows, negotiate blocking, manage locks, and transmit results. Every FETCH being executed has about the same performance of executing a SELECT statement.A cursor should only be considered in situations where you need to scroll through a set of rows, and then based on criteria you specify, do something potentially different to each row (and in many cases, even this can be done using a standard query) "Ways to avoid using a cursor include:1. Rewrite the cursor as a normal query. Cursors that perform the same task over and over on a set of records are a waste of server resources because this could be handled by a standard query. Case statements can also be used where each row is conditional on data in a row.2 Rewriting the cursor using temporary tables 3 Use derived tablesThere are also plenty of article on the web for reasons to avoid using cursors. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-01-13 : 11:46:25
|
Here's my opinion, and some of my cohorts here will loudly dissagree I'm sure. In a production application environment, where there are concurrent requests, sql transactions, and the faster the better is the rule, then cursors should not be used. However, when you want locked resources and don't mind slow performance cursors can be an effective tool for highly controlled row by row processes. It is extremely rare that the developer/dba finds themselves in that situation though. I've also used them just to insert some test data or other non-deployed things in a development environment. All it is is another tool. I have a chain saw at home but rarely find the need for it. I don't use it every time I want to chop some vegies  Be One with the OptimizerTG |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-13 : 12:01:45
|
| Can anybody tell me any usefulness in Cursor.U all r telling about disadvantagesTG, ur chainsaw is useful in some instances atleast isn't it?And if it makes easier to do the same task more effectively with some other tool, y don't u throw it away ?Again my concern is not that y it is in T-SQL (lot of obsolete things may be there). My concern is :Isn't there a single benefit in using Cursors!! |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-01-13 : 12:06:58
|
| re-read my post. I say that I personally do use cursors and, like my chainsaw, use them periodically for a simple way to insert test data or for highly controlled row by row processing that won't interfere with concurrent usage.Be One with the OptimizerTG |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-13 : 13:00:46
|
quote:
Originally posted by Srinika
Can anybody tell me any usefulness in Cursor.U all r telling about disadvantagesTG, ur chainsaw is useful in some instances atleast isn't it?And if it makes easier to do the same task more effectively with some other tool, y don't u throw it away ?Again my concern is not that y it is in T-SQL (lot of obsolete things may be there). My concern is :Isn't there a single benefit in using Cursors!!
Cursors are sometimes the only method for executing dynamic SQL code against multiple database objects.There, I said something nice about cursors. Now I need to go rinse with some mouthwash... |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-01-13 : 13:03:37
|
quote:
Originally posted by blindman
quote:
Originally posted by Srinika
Can anybody tell me any usefulness in Cursor.U all r telling about disadvantagesTG, ur chainsaw is useful in some instances atleast isn't it?And if it makes easier to do the same task more effectively with some other tool, y don't u throw it away ?Again my concern is not that y it is in T-SQL (lot of obsolete things may be there). My concern is :Isn't there a single benefit in using Cursors!!
Cursors are sometimes the only method for executing dynamic SQL code against multiple database objects.There, I said something nice about cursors. Now I need to go rinse with some mouthwash...
What about that xp that allows you to execute a recordset full of sql statements...  Corey Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."  |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-01-13 : 14:49:22
|
| r u saying that u r n-noid by Srinika's writing style? y not, me 2!Be One with the OptimizerTG |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-13 : 21:53:49
|
| Arnold & TG,I am very sorry, if you feel I annoyed you.I am using it, to save some time in typing.I thought when we discuss about technical matters these kind of usage is acceptable.Anyway forgive me for my writing style, as I am slow in typing (which is the main reason for me to use my "Syntax") |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-01-13 : 23:09:34
|
| Don't lump Arnonld in with my incensitivity. He never said anything mean (like I did). It's harder for me to read but I'd rather have you (and your "Syntax") participating then no you at all peace. Just be prepared for a little teasing.Be One with the OptimizerTG |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-14 : 01:35:34
|
| >> "U all r telling about disadvantages">> "TG, ur chainsaw is useful in some instances atleast isn't it?">> "y don't u throw it away ?"It may save typing time, but my brain just sees a syntax error.I find it irritating and more difficult to read, so I'm much less likely to read or respond.CODO ERGO SUM |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-01-14 : 04:52:23
|
| http://msdn2.microsoft.com/ms172364.aspxhttp://www.sqlteam.com/item.asp?ItemID=11842 |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-14 : 08:03:11
|
| Arnold & TG, I am prepared for any teasing. I couldn't figure out know whether its serious or not, for safety, I selected it as serious. So no hard fealings ?Michael Valentine Jones,From what you say, what I got is, Whenever I ask a question its always better to write in correct English. But when answering a question its not that a bad idea.Because the one who expects the answer, never mind, spending some of their CPU cycles to interpret my (in this case I have seen lot of people are using this kind of abbrevations these days) syntax.When I want to get an answer, I should spend my time for typing, and make least trouble as possible to those who answers, for the question to be attempted by a larger audiance.TG & afrika,Thanks for the answers |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-16 : 03:33:33
|
As blindman said, cursors can be used if you want to run some scripts on multiple databasesYou can see some of the system procedures have been written using Cursorsselect text from Master..syscomments where text like '%cursor%'Look for the code used at those sps>> "U all r >> "TG, ur >> "y don't u Chat Lover  MadhivananFailing to plan is Planning to fail |
|
|
|
|
Benefit (if any) of Cursors