SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 WTF *is* a cursor, anyway?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stephenbaer
Yak Posting Veteran

USA
71 Posts

Posted - 09/22/2006 :  15:56:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/22/2006 :  16:04:49  Show Profile  Reply with Quote
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/22/2006 :  16:08:01  Show Profile  Reply with Quote
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).
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 09/22/2006 :  17:42:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000