Home Forums SQL Server 2012 SQL Server 2012 - T-SQL How to quickly update/insert 3M records in a large table? RE: How to quickly update/insert 3M records in a large table?

  • March 30, 2015 at 10:12 am

    #1787411

    Jason,

    I've changed the code from delete & insert to MERGE, and I'll get the test result tomorrow.

    Why do you prefer CLUSTERED index to NON-CLUSTERED one? I guess it would cost more I/O while importing data from the data file, for data in data file is not ordered.

    Jason-299789 (3/30/2015)

    Effectively step 3 is redundant why delete the rows in the source, and then reinsert them, you're doubling the work load. Its should just be a case of

    1) Import data

    2) Update Changed Data

    3) Insert New Data.

    I agree with Eirikur, It sounds like a good candidate for MERGE.

    I would also suggest that the key on both the destination table is clustered it should make the process significantly faster. so something like.

    ----

    @pietlinden, there will be no benefits of partitioning and then using Switch, as you cannot guarantee that the data being altered will all be in the same partition frame, and 10 million rows really doesn't justify the additional overhead of partitioning.