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 |
|
jp2code
Posting Yak Master
175 Posts |
 Posted - 2009-01-29 : 09:13:58
|
One of the guys here at work likes to work with Cursors. He does not teach very well, but from what I've gathered, a cursor does about the same thing as the while loops that I use.Recently, I was asked outside of work what a Cursor was and what the difference was between a Server Side Cursor and a Client Side Cursor. I have no idea!Could someone tell me?
Avoid Sears Home Improvement |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-01-29 : 10:41:10
|
| I will tell you to generally avoid them like the plague. Set based methodology is the way to go.On a rare occasion, cursors can be useful, but they are generally only useful to people who don't understand SQL. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-29 : 10:50:02
|
Every cursor uses temporary resources to hold its data. These resources can be memory, a disk paging file, temporary disk files, or even temporary storage in the database. The cursor is called a client-side cursor when these resources are located on the client machine. The cursor is called a server-side cursor when these resources are located on the server machine.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-29 : 10:51:35
|
http://www.sqlteam.com/article/cursors-an-overviewWebfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-01-29 : 23:07:26
|
| I'll elaborate on my standard cursors-are-tweezers analogy to illustrate client vs. server cursors. It may take a minute or three, but bear with me:You want a cup of coffee, and you want to add sugar to it.You can use a spoon to put the sugar in. Or you can use tweezers.The most efficient way is to dump the spoonful of sugar into the coffee. That's a set-based operation. On the other hand, cursors are tweezers. They only work with a single row (grain of sugar) of a set (teaspoon), instead of the whole set at once. It's rather obvious where the inefficiency lies: the overhead of moving one grain at a time far exceeds the effort of moving the whole spoon once. This is the general wisdom for avoiding cursors.Now to demonstrate this for client and server sides:You go to a coffee shop to get your coffee, with sugar. The server (get it? server?) at the counter then picks up their tweezers, moves one grain at a time to the cup, and when they finish they hand you a sweetened cup of coffee. That's a server-side cursor.Now imagine the server hands you the sugar (and optionally the tweezers) and YOU pick one grain at a time to put in your coffee. That's a client-side cursor.If you think I'm jesting, let me show the relative (REALLY relative) advantages and disadvantages: in the server side cursor, the work is done by a specialized device/person, and minimizes "network traffic". You get only the cup of coffee, already sweetened, with minimal effort on your part. The downside is the server is busy tossing sugar into your cup. Multiply this by a dozen coffee shop customers, and the waiting time becomes infinite...ly obvious.For the client-side cursor, the advantage frees the server to do other things, like get another cup of coffee for someone else. It can hand the bowl of sugar over to you (a set-based operation) and let you twiddle grains; its job is done. This advantage has a downside though, in that more sugar (data) is sent than may be needed, with the rest thrown away. Even if this traffic is limited to just the amount you need, you still have to coordinate spoons and tweezers (CLOSE and DEALLOCATE) in addition to one-grain-at-a-time motion.This may sound like a dumb example, but it fits very closely to how the relevant operations actually work. As useful as the tweezer analogy is, it's more important to see the spoon as a set-based operator: it grabs the sugar you need, and works with it as one object, not as a collection of tinier objects. This seems to be an extremely difficult concept for most programmers to understand. You don't really care about the actual grains of sugar, you just want a teaspoon's worth in your cup. And further, a spoonful of sugar is a set of its own, even if it's a subset of a larger bowl or 5 lb. bag of sugar. Once it's in the spoon, the rest of the superset doesn't matter to you.What's even more ironic/ridiculous about cursors, to me anyway, is that you actually take a spoonful of sugar, then use tweezers to pick it from the spoon grain by grain. (look at the DECLARE CURSOR statement) That's another of the reasons I like this analogy; it clearly shows the mechanics of the two operations and illustrates their strengths and weaknesses, and in real-world terms even a child would find obvious.Hope this helps. By the way, next time you go to an interview where they ask about cursors, please laugh in their face, ask why they'd do such a thing, then tell them this story. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-30 : 01:23:26
|
 WOW! Great story! Great Example! I love it!Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jp2code
Posting Yak Master
175 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-02 : 06:56:46
|
quote:
a cursor does about the same thing as the while loops that I use.
Your while loops might be slightly more efficient that the cursors but they still aren't a set based op. It won't matter in the slightest if your while loop is 10% faster than his cursor if you still could have got a huge speed increase by rewriting in a set based way in the first placeIt all depend's what king of things you are doing. Without a use case scenario we can't really say yea or nay -- if you *have* to do a loop it usually isn't going to matter whether that's done in a cursor or a while loop -- it's going to be slow either way.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Cursors