Nested Cursors Suck!!! Oh wait, so do non-nested cursors.

Well, I have to start with something, so here it is.  I would say I hope you enjoy it, but I don't really care.  I'm just grumbling about work.  (grin)

At work the last couple weeks, I worked on fixing an old nightly batch update process that ran about 6.5 hours each night.  When I analyzed it, I saw a small problem, it had (count them) nested cursors FIVE LEVELS deep.  After rewriting the query to be two stored procedures with two monster select statements it ran in about 44 seconds.

Lessons learned: 

  • Always think set based. 
  • Loops are sometimes needed, but a lot of times you can do it with a couple big set statements and save a lot of resources.   
  • People who don't take time to actually learn the language they are using are stupid.
  • Cursors Suck!!!

I don't know how many times I've seen people use cursors because they can't find a way around it.  If anyone is actually bothering to read this, you RARELY (ie. never if you're a normal, everyday developer which you are regardless of how grand you think you are) have to use a cursor.

Instead of a cursor use a while loop.  If you need an example, go to www.sqlteam.com.  They have millions and millions of them.  If you don't believe me count them.  I'll give you 50 cents for proving me wrong.

Favorite words used: 2 (stupid, suck)

Mean level (1-10):  3 (I'm being nice since it's my first post.)

Education level (1-10):  1 (If you learned something from this, you need to buy “Transact SQL for Dummies”

Entertainment level (1-10): -5 (No comment)

Print | posted on Monday, March 22, 2004 11:13 PM

Comments on this post

# Kent Tegel

Requesting Gravatar...
Kent Tegel
Left by Pingback/TrackBack on Mar 23, 2004 9:25 PM

# SQL Team Weblogs

Requesting Gravatar...
SQL Team Weblogs
Left by Pingback/TrackBack on Mar 23, 2004 9:25 PM
Comments have been closed on this topic.