| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2009-11-06 : 16:05:32
|
| I told in the recent interview that I avoid cursors because they cause a network round trip. And I was challenged. Now after the interview, I think the n/w round trip is True for the app cursors, not the SQL server cursor, which resides on the database layer. What are the down sides of using a SQL cursor, compared with a temp table & while loop?------------------------I think, therefore I am - Rene Descartes |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-06 : 17:03:17
|
| dude....cursors in sql server, reside in sql serverI imagine, and I could be wrong, that "Cursors", i.e. looping through a result set is purely done on the app serverNo round trips (unless your app side "Cursor" is executing a sql server statement for each row, in which case you should be shot)No the downside of Cursors in SQLServer is that they incur a lot of overhead and must access data row by row (does a page of data get loaded after n fetches? Gotta look up Delaneys book).You are much better served to use a set based approach to your DML and be appropriately indexed.Now, performance issues on the app side with your "Cursor" would be bad if you decide to select 50,000 rows...that would be a problemUnderstand also, this is NOT True for DB2 and Oracle which are optimized for cursors (they use pointers to data and don't actually move anything)HTHor MOOor As Much as I can correctly remember being old and decrepitBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-06 : 20:05:21
|
| I seem to post this a lot, sorry if it's getting old:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118835 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-06 : 22:45:03
|
Yeah, you did, I'm just being a shameless self-promoter.  quote:
Understand also, this is NOT True for DB2 and Oracle which are optimized for cursors (they use pointers to data and don't actually move anything)
If they don't actually move anything, how do the rows get sent to the client? I'm not sure this would be the reason those cursors are faster, since they'd have to access the data pages on each fetch, whereas SQL Server could fetch on buffered pages. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2009-11-09 : 10:16:37
|
quote:
Originally posted by robvolk
I seem to post this a lot, sorry if it's getting old:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118835
Rob, Thanks a lot for the, Coffee-Sugar example. I am very clear now! I am going to use your analogy in my next interview, however I will portray as if it is my own. Hope that is fine with you!------------------------I think, therefore I am - Rene Descartes |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-09 : 17:34:43
|
| Oh yeah, take all the credit you want. Like I said though, please laugh at them before you tell the story. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2009-11-10 : 09:42:01
|
quote:
Originally posted by robvolk
Oh yeah, take all the credit you want. Like I said though, please laugh at them before you tell the story.
The one who took my interview was a Geek, but also a nice guy. I guess he just wanted to know, how much clear I am with my concepts. End of the day, I am clear about the curors. That's a good news! Thank you guys. (I just got the offer! )------------------------I think, therefore I am - Rene Descartes |
|
|
|
|
Interview question: Cursors