how to update millions of records quickly........

Question

how to update millions of records quickly........

Viewing 11 posts - 16 through 25 (of 25 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Jeff Moden (7/29/2010)
Bhuvnesh (7/28/2010)
Jeff Moden (7/28/2010)
you'd need a loop for that.
or may be batch approach can make work smoother
The loop would control "batches". That being said, what do you mean by a "batch approach"?

Records in bunches(sets).

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

Bhuvnesh (7/30/2010)
Jeff Moden (7/29/2010)
Bhuvnesh (7/28/2010)
Jeff Moden (7/28/2010)
you'd need a loop for that.
or may be batch approach can make work smoother
The loop would control "batches". That being said, what do you mean by a "batch approach"?
Records in bunches(sets).

We're talking about the same thing, then.:-)

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

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 3

Thanks a lot...

@jeff..

but by loop I can divide my 50 million into 10 parts and update them one by one ...Is that right?

@Bhuvnesh

I have never used batches...how to do by batches, same thing using a loop and keeping a counter.??

Thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

ssis learner__ (8/2/2010)
Thanks a lot...
@jeff..
but by loop I can divide my 50 million into 10 parts and update them one by one ...Is that right?
@Bhuvnesh
I have never used batches...how to do by batches, same thing using a loop and keeping a counter.??
Thanks

Yep... by loop you can divide the 50 million although I'd recommend dividing it into 20 or 25 parts. And, yes... that's how you would "do by batches".

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Hmm, I thought the quirky update WAS primarily a performance tool since it can solve problems where you need a triangular join or actual TSQL cursor to get the right result (i.e. running totals for example).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

TheSQLGuru (8/3/2010)
Hmm, I thought the quirky update WAS primarily a performance tool since it can solve problems where you need a triangular join or actual TSQL cursor to get the right result (i.e. running totals for example).

Hi Kevin....a "note of disapproval"..maybe?....though I am far from qualified to make a judgement

If its "wrong" to use the quiirky update in this manner....what would be the better method please?.

Kind regards Graham

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

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

TheSQLGuru (8/3/2010)
Hmm, I thought the quirky update WAS primarily a performance tool since it can solve problems where you need a triangular join or actual TSQL cursor to get the right result (i.e. running totals for example).

It is. It can also be used the way Graham used it. I call that way a "data smear" and I was going to demonstrate it in a follow up article. It can be extremely effective especially when you compare it to other methods that use even an effective self join for the 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)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

gah (8/3/2010)
If its "wrong" to use the quiirky update in this manner....

Absolutely not wrong. Well done, Graham. Heh... I was going to write a second article on other uses of the quirky update. The method you used is what I refer to as a "data smear". As you've seen, it's very effective especially when compared to some self joins.

Considering all that, you want to write Part Two and I'll play tech reviewer and editor for you? 😉

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Jeff Moden (8/5/2010)
use the quiirky update in this manner....

Any article link ??

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

Bhuvnesh (8/5/2010)
Jeff Moden (8/5/2010)
use the quiirky update in this manner....
Any article link ??

http://www.sqlservercentral.com/articles/T-SQL/68467/

have also corrected link in my original post.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

Jeff Moden (8/5/2010)

Considering all that, you want to write Part Two and I'll play tech reviewer and editor for you? 😉

Jeff....I have found that I prefer to read articles from authors who have a sound understanding of the subject matter and the ability to explain sometimes complex issues in a way that is easy to follow and digest....

...therefore that precludes me from writing anything about anything ...ever 😛

But I will be looking out for your article when you publish it.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day