Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Cursors....How to get away from using?

    Hey guys

    I have heard cursors are not the way to go. But I am wondering if/how to get out of a situation that I am using a cursor in...in order to make my stored proc run more effieciently.

    I am quite novice in my abilities and I am completely stumped on how to get around using them.

    As far as INSERTs go, I think I can work around that, but how would I write UPDATE statements for all lines of a table to say pull a key from another table to reference them together?


    I usually make my SELECT statement in the cursor, then update against the criteria from the SELECT statement. Now this is quite a slow process when I am updating 100K records.

    Any help or pointers or a link to a good tutorial would be woderful.

    Thanks
    tibor

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    cursors are on the rare occassion the right way to go. incrementing totals for example. or if the situation requires row by row processing like you need to fire an extended stored procedure.

    what you want to read about though is set based processing.

    tell me, can you take your select statement and move the from and where clause to the update statement to create an UPDATE FROM statement? Bye bye cursor.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You might want to take a look at the documentation. Take a look at examples C and F. Example C (from clause) works in both SQL Server 2000 and SQL Server 2005, although not very well documented for SQL Server 2000. Example F (Common Table Expression) works only in SQL Server 2005, but I think it will potentially perform better in some cases. I have not verified this though.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Mar 2006
    Posts
    82
    Great, thanks guys!

    The "Using UPDATE with the FROM Clause" in the documentation was exactly what i needed....it took 30 seconds vs 1.5 hours, haha.

    I appreciate the help very much.

    tibor

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I always love some of the subject titles

    Like my response for this one (and don't get offended) would have been..

    "Leave the IT Business"

    But i'm glad you got what you needed.

    Now, post the code so we can make it really fly
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2006
    Posts
    82
    Well, no offense taken...but dont assume that because I asked a SQL question that I am in the IT business.

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Well, since your query used quite some amount of time, I DO assume that you have quite a bit of data as well, and you appears to work on some data in or from some kind of business. If that is correct, well... I'm glad I could help, but I would be concerned about what you can end up doing. Databases are not to play with, and as you have noticed, a badly written query may cause the server working for hours, or even days. Please keep that in mind.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •