Results 1 to 6 of 6

Thread: T SQL Question - Cursors vs. Select

  1. #1
    I like cold beverages BumperSticker's Avatar
    Join Date
    Mar 2003
    Location
    Aww nuts...here again?
    Posts
    65

    T SQL Question - Cursors vs. Select

    A question for all you T-Sql guys.

    Heres my problem:

    I open a cursor to a [large table]. I have to run through each record , do some math on some of the fields based on entries in other fields etc etc.

    I then open up a cursor to a [main table] where the info is stored. I pull up a matching record based on a number (serial number ) then make some more calculations and then finally store the results in the [main table] using the current cursor position.

    I then close the cursor, deallocate then loop back to the beginning of the procedure to fetch the next record in the [large table] open the cursor to the [main table] etc etc.

    Two questions:

    #1. Speed being my main concern , should I use a Select and Update rather than a FETCH and UPDATE ??

    #2. If I run the procedure in the Query Anaylizer It takes about an hour and 45 mins to process 139,000 records. When I run the exact same code as a stored procedure on the server and trigger it with the server agent it takes 6+ hours!! One thing I did notice. Using performance monitor when I watch the CPU usage and other attribs on the server when I run it in SQL anaylizer its a nice steady usage but again when I run it as a strored procedure its choppy, up and down spikes which I am assuming is a side effect of me running it on the server. I would have expected it to run quicker as a tored procedure. Any ideas there?

    If I can solve #2 , #1 is no longer a concern.

    Thanks guys!!
    I want a computer so good it must be used for evil. I want to walk out of Apple SoHo with a golden aura. I want anti-gravity bluetooth. I want a computer that chases squirrels and steals your picnic lunch. I want a computer that gets bored while I'm at work and prank-calls Microsoft tech support. I want WiFi control over my coffee maker. And my roommate. And the cat. I want the ability to hijack passing iPods. I want a computer that reduces fat while locking in savory chicken flavor. I want four on the floor and a fuzzy 970 chip hanging from the rearview. I want an Airport station that cleans my laundry with sonic waves. I want a keyboard with gold teeth. I want fries with that.

    Folding@Home

  2. #2
    SG Enthusiast
    Join Date
    Jan 2001
    Location
    DC
    Posts
    4,717
    Not enough info to answer #2, but select into is much faster than fetching.

    Cursors in general are expensive, too, but that doesn't answer #2

    I am not an MS guy, so my comments are generic to sql.

    EDIT: though somewhat dated, this seems relevent http://www.codeproject.com/cs/databa...&select=981122
    anything is possible - nothing is free


    Quote Originally Posted by Blisster
    It *would* be brokeback bay if I in fact went and hung out with Skye and co (did I mention he is teh hotness?)

  3. #3
    I like cold beverages BumperSticker's Avatar
    Join Date
    Mar 2003
    Location
    Aww nuts...here again?
    Posts
    65
    Thanks man!! Although dated it got me on the right track to look at a few things and I think I found a major contributer to the lack of speed. This will help alot.
    I want a computer so good it must be used for evil. I want to walk out of Apple SoHo with a golden aura. I want anti-gravity bluetooth. I want a computer that chases squirrels and steals your picnic lunch. I want a computer that gets bored while I'm at work and prank-calls Microsoft tech support. I want WiFi control over my coffee maker. And my roommate. And the cat. I want the ability to hijack passing iPods. I want a computer that reduces fat while locking in savory chicken flavor. I want four on the floor and a fuzzy 970 chip hanging from the rearview. I want an Airport station that cleans my laundry with sonic waves. I want a keyboard with gold teeth. I want fries with that.

    Folding@Home

  4. #4
    SG Enthusiast
    Join Date
    Jan 2001
    Location
    DC
    Posts
    4,717
    Glad that helped. I saw a few things in that article that impressed me. The hardest part about SQL for those who have experience in other languages is to focus on what you want vs. how to do it - the true advantage of SQL.

    I wasn't sure if the concept of implicit vs. explicit cursors applies to MS. In Oracle. It's usually better to leave it up to the DB Engine to determine the most efficient path - with SELECT INTO, you are using implicit cursors - assumes your schema was designed efficiently in the first place, tho

    Have fun,

    Skye
    anything is possible - nothing is free


    Quote Originally Posted by Blisster
    It *would* be brokeback bay if I in fact went and hung out with Skye and co (did I mention he is teh hotness?)

  5. #5
    I like cold beverages BumperSticker's Avatar
    Join Date
    Mar 2003
    Location
    Aww nuts...here again?
    Posts
    65
    Actually it worked out great. I got it down to processing 130k records in 27 mins. Doesnt sound fast but for all the checking and calculations I have going on its great.

    Thanks again
    I want a computer so good it must be used for evil. I want to walk out of Apple SoHo with a golden aura. I want anti-gravity bluetooth. I want a computer that chases squirrels and steals your picnic lunch. I want a computer that gets bored while I'm at work and prank-calls Microsoft tech support. I want WiFi control over my coffee maker. And my roommate. And the cat. I want the ability to hijack passing iPods. I want a computer that reduces fat while locking in savory chicken flavor. I want four on the floor and a fuzzy 970 chip hanging from the rearview. I want an Airport station that cleans my laundry with sonic waves. I want a keyboard with gold teeth. I want fries with that.

    Folding@Home

  6. #6
    SG Enthusiast
    Join Date
    Jan 2001
    Location
    DC
    Posts
    4,717
    Quote Originally Posted by BumperSticker
    ... I think I found a major contributer to the lack of speed. This will help alot.
    Could you elaborate a little? I'm curious now

    Skye

    EDIT: Still not exactly sure what you are doing, but heavy lifting on 130k records under 30 mins is pretty smokin~
    anything is possible - nothing is free


    Quote Originally Posted by Blisster
    It *would* be brokeback bay if I in fact went and hung out with Skye and co (did I mention he is teh hotness?)

Similar Threads

  1. Quick Question...
    By exesion in forum General Discussion Board
    Replies: 1
    Last Post: 06-18-01, 07:09 PM
  2. Rock-n-Roll question for the EXPERTS.
    By ghost in forum General Discussion Board
    Replies: 10
    Last Post: 06-10-01, 11:42 AM
  3. Colour and EvilAngel Look here, its about my partition question
    By striderf1 in forum General Discussion Board
    Replies: 19
    Last Post: 04-18-01, 03:47 AM
  4. a question if i may
    By minir in forum Distributed Computing
    Replies: 3
    Last Post: 03-31-01, 04:55 PM
  5. Ethical question
    By magg in forum General Discussion Board
    Replies: 4
    Last Post: 02-27-01, 06:45 PM

Bookmarks

Posting Permissions

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