Ideas for Huge Update using SSIS

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

  • 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 (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 ???

  • 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

  • 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

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

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