Ideas for Huge Update using SSIS

Question

Ideas for Huge Update using SSIS

vignesh.ms

Hall of Fame

Points: 3456
More actions
June 10, 2014 at 10:37 am

#287425

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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
Consultant - Straight Path Solutions
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

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

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 SSC Guru Points: 259105 More actions · Answer 3

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

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