SQL Server Performance

Retrieving data from cursor

Discussion in 'T-SQL Performance Tuning for Developers' started by Chappy, Jan 5, 2003.

  1. Chappy New Member

    I have a stored procedure which opens a very generic cursor (based on sql built from the supplied procedure parameters). The problem im having is this is very generic and so the field list often varies.
    Currently im having to write dynamic sql to define corresponding local variables and then loading the data from the cursor into these locals.

    This is a bit of a pain, Im wondering if there is any way I can access the row the cursor is pointing to directly (by fieldname), without having to FETCH INTO some locals?

    Thanks.
  2. royv New Member

    I don't think you can do this. However, maybe you can rewrite the procedure to not use a cursor. I always try to avoid cursors whenever possible.


    "How do you expect to beat me when I am forever?"
  3. Chappy New Member

    ok, thanks royy.

    Sadly I wish it was possible to rewrite without cursors. The sp takes dynamic SQL which is then executed, and a counter is then tallied for the rows in order (imagine a series of bets on horses, the sp needs to tally the biggest winning and losing streak, which is dependant on the order the records are processed, hence requires a cursor).
  4. bradmcgehee New Member

    Have you tried temp tables instead of a cursor?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. Chappy New Member

    Im not sure thats really possible. I could fill a temp table but then id still have to open that with a cursor so I can find the longest losing streak (chronologically), where each row in the dataset corresponds to a bet which either wins or loses. Unless im missing something <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />
  6. bradmcgehee New Member

    Have you considered the possiblity of looping through a temp table using WHILE statement. Don't know if it will work in your case, just something else to consider. This way, you might be able to get rid of the cursor.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  7. Chappy New Member

    I hadnt considered that, but it looks like it could be very useful. Thanks for the tip!
  8. royv New Member

    Instead of a cursor, no matter how tempting, I always use a temp table with a WHILE loop. Cursors are known for their performance problems, and unless I have some code that is only going to run once, I never use cursors. The WHILE code looping through a temp table is a more valid solution now because there are table variables in SQL 2000 which are in memory and really fast!


    "How do you expect to beat me when I am forever?"
  9. rajoo New Member

    Hi

    I just posted a question about CURSORS in the "Performance Tunning for DBA" and looks like I should have done it here also.

    Would you mind giving a sample of how a curser and be placed with a WHILE statement.

    Thanks

Share This Page