none
Very different insert time RRS feed

  • Question

  • Hi, folks

    I have to migrate our company SQL server to Germany. I have new SQL server 2014 installed in Germany and I started to transfer databases and applications. I noticed strange behaviour....
    I have same database on local SQL and also remote SQL in GE.

    Login at local SQL and command run from local SQL

    Insert into remote SQL server – read from local server - tooks 00:01:54
    INSERT INTO [remotesrv].remotedb.[dbo].[remotetbl_TEST]
    SELECT * FROM [dbo].[local_Company]

    Insert into local SQL server – read from remoteSQL server - tooks 00:00:01
    INSERT INTO [local_company]
    SELECT * FROM
    [remotesrv].remotedb.[dbo].[remotetbl_TEST]

    ===============================================================

    Login at remote SQL, command run from remote SQL:

    Insert into local SQL server – read from remote SQL server - tooks 00:02:00
    INSERT INTO
    [remotesrv].remotedb.[dbo].[remotetbl_TEST]
    SELECT * FROM [dbo].[Company]

    Insert into remote SQL server – read from local SQL server - tooks 00:00:01
    INSERT INTO [Company2_TEST]
    SELECT * FROM
    [remotesrv].remotedb.[dbo].[remotetbl_TEST]

    In another words PUSH the data and write to the remote SQL server is more than 150x slower than let the remote server PULL the data and write it down into its local table. Why? Is any possibility to solve this situation? I also found, that PUSH makes lot of small packets (300) in the WAN network, but PULL is using network packet size of 4096. How to avoid this packet segmentation and size change?

    Many thanx


    • Edited by Faskaloid Friday, July 29, 2016 12:44 PM
    Friday, July 29, 2016 12:20 PM

Answers

All replies