copying data between servers

  • paul Arscott

    Mr or Mrs. 500

    Points: 504

    Im trying to insert some records into server B from server A via a linked server.

    (eg. Insert into ServerB.DBName.dbo.TargetTable Select A,B,C From SourceTable.....)

    Inserting locally takes about 1 minutes for 1million records.

    Inserting into server B takes 16 minutes for 1 million records with the example above.

    Interestingly it only take about 1 minute to insert into server B with DTS. Is there a way to get the same DTS performance with native T-SQL?

    Paul

  • GSquared

    SSC Guru

    Points: 260824

    In my experience, procs that move data from one server to another tend to be slow. It's the overhead for managing large transactions across the network.

    I've generally had much better success with SSIS/DTS.

    The one way I've found to get around this is to first move the data into a table variable, then move it to the other server. Since table variables bypass logging and transactions, they do seem to be faster for this.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Grant Fritchey

    SSC Guru

    Points: 395866

    We've found the fastest way is to export from one database out to an SSIS RAW file and then back into the next database in the next server. Storing the RAW file on the destination server seems to work best.

    If you really need to use TSQL, you can look at the BULK INSERT statement.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Anam Verma

    SSCertifiable

    Points: 5365

    The one way I've found to get around this is to first move the data into a table variable, then move it to the other server. Since table variables bypass logging and transactions, they do seem to be faster for this.

    is it advisable to store large dataset into table variables. I am of a view (may be wrong) that we should use table variable only when dataset is small.

  • jezemine

    SSCrazy Eights

    Points: 8059

    if you are moving millions or rows around, use a bulk method: BCP/Bulk Insert/SqlBulkCopy/SSIS. don't mess around with table variables.

    ---------------------------------------
    elsasoft.org

  • ae299

    Grasshopper

    Points: 15

    But how do you copy data from TABLE to TABLE on a remote server

    as mentioned in the original post?

    BULK INSERT / BCP seem to load OS files only, am I correct?

  • GSquared

    SSC Guru

    Points: 260824

    helloanam (4/9/2008)


    The one way I've found to get around this is to first move the data into a table variable, then move it to the other server. Since table variables bypass logging and transactions, they do seem to be faster for this.

    is it advisable to store large dataset into table variables. I am of a view (may be wrong) that we should use table variable only when dataset is small.

    Generally true. The reason is that you can't build indexes (other than the clustered index and PK) on them, and they don't have statistics.

    For a simple data transfer, neither of those things matters.

    For any more complex query, they both do matter.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared

    SSC Guru

    Points: 260824

    ae299 (4/10/2008)


    But how do you copy data from TABLE to TABLE on a remote server

    as mentioned in the original post?

    BULK INSERT / BCP seem to load OS files only, am I correct?

    The problem is that direct table to table copying takes a long time.

    Exporting the data to a .txt file, then bulk inserting it on the other server, will be faster that a table to table copy.

    You're right that these need to work on external files (not directly from the database), but that's why you do the export step first.

    Basically, it's a way to avoid distributed transactions, which slow down large transfers of data between servers.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 8 posts - 1 through 8 (of 8 total)

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