Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Ideas for Huge Update using SSIS Expand / Collapse
Author
Message
Posted Tuesday, June 10, 2014 10:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:46 AM
Points: 135, Visits: 373
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..
Post #1579319
Posted Tuesday, June 10, 2014 10:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #1579320
Posted Wednesday, June 11, 2014 4:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:46 AM
Points: 135, Visits: 373
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 ???
Post #1579566
Posted Wednesday, June 11, 2014 4:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,283, Visits: 10,156
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1579567
Posted Wednesday, June 11, 2014 6:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 2,901, Visits: 2,927
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,
SQL Server developer at Seavus
www.seavus.com
Post #1579588
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse