• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 22 views
  • Users 0 members are here
Related

We don't need no stinkin' cursors!

Ryan Creps (Past Staff Member)
$organization

I was moved by the Tessi-Spirit this morn' to evangelize all ye SQL nuts out there.  If you have an interest in furthering your T-SQL skills, a good best practice to learn is how to avoid the use of cursors.  Here's a good demonstration of the overhead of using them:

http://www.sqlservercentral.com/articles/cursors/65040/

  • $organization
    Good link Ryan.

    When setting up our development environment with a new copy of the live
    database, we run a procedure to delete or mangle any personally
    identifiable customer information, as the environment is not as secure as
    our others. The old procedure was a series of cursor driven while-loops
    running over each customer record. On the old hardware of our dev system
    it took upwards of eight hours. I attacked the procedure last month,
    stripping out all the cursors I could, and have brought it down to about
    twenty minutes.

    On 18 Jun 2010 09:16:54 -0500, "Ryan Creps"
    wrote:
    > I was moved by the Tessi-Spirit this morn' to evangelize all ye SQL nuts
    > out there.� If you have an interest in furthering your T-SQL skills, a
    > good best practice to learn is how to avoid the use of cursors.� Here's
    a
    > good demonstration of the overhead of using them:
    >
    > http://www.sqlservercentral.com/articles/cursors/65040/
    >
    > --
    > View this message online at
    > http://www.tessituranetwork.com/COMMUNITY/forums/p/2743/9326.aspx#9326
    [1]
    > or reply to this message
    > --
    > This message was sent automatically to you by www.tessituranetwork.com
    > because you subscribed to the Tessitura Technical Forum. You may reply
    to
    > this message to post to the Technical forum or visit the site to search,
    > read and post to the forums. In the interest of keeping the forum posts
    > from becoming cluttered, we encourage you to delete previous message
    text
    > from your reply before sending. Thank you!
    >
    > Links:
    > ------
    > [1]
    http://www.tessituranetwork.com/COMMUNITY/forums/p/2743/9326.aspx#9326

    --
    Gawain Lavers f. 510.643.6707
  • $organization in reply to Gawain Lavers

    Ryan,

    Great post. I've always been told to avoid cursors.  And it has absolutely given me better chops as a query writer.  Forces you to think of alternate paths to the same result.

  • $organization

    I would like to second the use of SQL Server Central as a resource in general. Since first tackling the language last year, it's proven to be a simultaneously humbling and empowering resource, with folks almost as helpful as the ones who populate this board.

  • $organization

    Just throwing in more praise of the avoidance of cursors. I've seen code go from taking 5-10 minutes or more to a few seconds, just by eliminating them.

  • $organization in reply to Chris Jensen

    I agree as well. Very rarely do I use them anymore.  Good link!