T-SQL question

Question

Post reply

T-SQL question

James00751

Grasshopper

Points: 20
More actions
August 28, 2007 at 8:18 am

#118326

Help appreciated:
I open up 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 yada yada.
Then I open up a cursor to a [main table] where all 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.
Question: Should I use a Select and Update rather than a FETCH and UPDATE (speed being of biggest importance)
Thanks in advance
James

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Techrecruit SSC Veteran Points: 276 More actions · Answer 1

I would guess select into would be much faster than fetching

Houston IT Jobs

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Show us the code, and we might help you rewrite it SET-based.

N 56°04'39.16"
E 12°55'05.25"

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Nested cursors on large tables, awesome!! If speed is your concern, scrap the cursors. I agree with Peter, post your code and work towards creating a SET-based solution.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

bledu SSChampion Points: 12335 More actions · Answer 4

update [main table]

set somecolumn = someCalculations

From [main table] A inner join

(select id, someLargeTableCalculations

from [LargeTable]

) B on A.someid = B.id

as has been said already post your code if you need a better solution

Everything you can imagine is real.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Well, what say thee, James... I see at least 3 people that really wanna help you fix this... all you need to do is post the offending code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

James00751 Grasshopper Points: 20 More actions · Answer 6

I got a co-worker to walk me through the code but greatly appreciate the offer for help. sorry for the late reply I got tied up the rest of the afternoon.

People prob don't say it enough but the posters on here are awesome (this excludes the bashers that eat people up for not replying back to a post the same minute)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

You feel "bashed" by anything on this thread? Most folks express some urgency when they post a question and your's is likely interesting... All I was doing was showing that I was interested in your problem...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bledu SSChampion Points: 12335 More actions · Answer 8

its a good kind of bashing because it shows people want to help you . maybe we should have a status on posts to denote urgency of a solution? that will probably appease the bashed and the bashers

Everything you can imagine is real.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Heh... yeah... and I'm still wondering why anyone would even use the term "bashing" in relation to what I posted. And I certainly wasn't trying to "eat at" anyone.

So, James, did your compatriot end up using either a Cursor or a While loop to solve your problem? Like I said, I am interested in your problem...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)