T SQL Question - Cursors vs. Select

[BumperSticker]

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!!

[cyberskye]

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/database/sqldodont.asp?df=100&forumid=13472&exp=0&select=981122

[BumperSticker]

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.

[cyberskye]

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

[BumperSticker]

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

[cyberskye]

... 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~