Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stephenbaer
Yak Posting Veteran
71 Posts |
 Posted - 2006-09-22 : 15:56:54
|
| I've been reading a lot about cursors. Apparently, they are bad. I looked it up on BOL, and I'm still not real clear on what they actually *are*. It seems that they perform something like a recordset clone in VBA, but then there is this fetch thing...???Is this how you create a 'table' that exists only in memory? Is this how a user can query a dynamically generated set of columns/records even if they don't have the permisiions they need to create a temporary table? Am I completely lost?-----------------Stephen |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-22 : 16:04:49
|
Yes, they are essentially like recordsets. They are a data access mechanism, certainly not a table that exists only in memory.The FETCH is like a MoveNext in a recordset, it just gets the next row. So the cursor maintains the row pointer to the recordset selected by the cursor DECLARE.They are bad because they need lots of resources to maintian that pointer, and often need to hold some kind of lock on all the rows in the recordset. Also they access rows one at a time which is usually hugely inefficient when SQL Server is capable of working with sets, imagine updating all the prices in a product catalog one at a time vs UPDATE SET ... WHERE ...Typically people who started using data with recordsets in languages like VB try to do everything with cursors because they are used to doing everything that way and haven't seen the light of set based processing  |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-22 : 16:08:01
|
| Oh, and I should have said - they are not a VBA recordset clone, if anything VBA recordsets are cursor clones, and cursors are sometimes used on the server to support VBA recordsets (not always because recordsets can also be created purely on the client side). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-09-22 : 17:42:58
|
| The analogy I like to use for cursors is:You have a cup of tea or coffee and you want to add sugar to it. Do you use a spoon, or tweezers? Cursors are tweezers.The idea is that a cursor works with only one row or record at a time, and you have to explicitly navigate to another row/record. This is akin to the tweezer picking up (fetching) one grain of sugar and dumping it into the cup. This navigation overhead is what makes cursors slow.The spoon does not deal with individual grains of sugar, but rather the entire set/spoonful of sugar as ONE entity, processed in ONE operation.Although this analogy seems rather straightforward to me I'm sure some people won't see the correlation to a computer database. I've had one or two people make really elaborate excuses about the tweezers being really stupid and how it's not the same with the computer. I've also likened cursors to painting a brick wall by removing a brick, painting it, and putting it back in the wall. This was considered even more stupid; irony or ignorance is their motto, I suppose.Application cursors, like DAO/ADO/ADO.Net recordsets, have a different programming model that requires them to work with individual rows. Even if they don't actually instantiate a server side cursor, they still perform single-row operations. Tracing a database as you run an ADO Recordset AddNew, Update, or UpdateBatch operation is very informative, especially if you compare all the available cursor, lock, and transaction options. It certainly cured me of ever using these methods (although SQL-DMO is far, far worse, trace Enterprise Manager and you'll see).I haven't done an ADO.Net one yet but I'm pretty sure it will be about the same. Even DataTables and DataSets are composed of collections of individual records. |
|
|
|
|
|
|
|
|
WTF *is* a cursor, anyway?