SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ideas for Huge Update using SSIS


Ideas for Huge Update using SSIS

Author
Message
vignesh.ms
vignesh.ms
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 516
Hi There,

I need to update 70 million records every day ..

updating this based on between condition



Source (70 million as of now): CardNumber varchar(14)

Destination (10 million as of now): CardNumber_From varchar(14) , CardNumber_To varchar(14)



create clustered index idx on source(CardNumber )
create clustered index idx on Destination(CardNumber_fr,CardNumber_to )

update Source set Customeraddress= des.address ,customermobilenumber = des.mobileno
from Source sor join destinaion des
on sor.CardNumber between des.CardNumber_fr and des.CardNumber_to




Suggest me any fast approach to tackle this situation..
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46603 Visits: 14925
First you have to define what you mean by fast.

My first suggestion would be to batch the updates using explicit begin and commit tran statements and do Top N where you find the batch size that works the best for your situation. Obviously you need a way to be sure you are updating a new TOP N each time.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
vignesh.ms
vignesh.ms
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 516
Jack Corbett (6/10/2014)
First you have to define what you mean by fast.
.



If I use my query runs about 3 to 4 hrs

I wanted to minimize the time of updation

So, I need some ideas to speed up the above query ..

any suggestions ???
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65222 Visits: 13298
In your original question, you posted two clustered indexes, which is not possible.
Which one is it?

Can you post the table DDL?

Some sample data is appreciated as well.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10655 Visits: 5158
Jack Corbett (6/10/2014)
First you have to define what you mean by fast.

My first suggestion would be to batch the updates using explicit begin and commit tran statements and do Top N where you find the batch size that works the best for your situation. Obviously you need a way to be sure you are updating a new TOP N each time.


I usually practice this approach and it's good. N often varies and mainly depends on the volume of the update and on the hardware.

Igor Micev,
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search