Linked Server Insert-Select Performance

Question

Assume that I have a table on my local which is Local_Table and I have another server and another db and table, which is Remote_Table (table structures are the same).

Local_Table has data, Remote_Table doesn't. I want to transfer data from Local_Table to Remote_Table with this query:

Insert into RemoteServer.RemoteDb..Remote_Table
select * from Local_Table (nolock)

But the performance is quite slow.

However, when I use SQL Server import-export wizard, transfer is really fast.

What am I doing wrong? Why is it fast with Import-Export wizard and slow with insert-select statement? Any ideas?

Answer 1

The fastest way is to pull the data rather than push it. When the tables are pushed, every row requires a connection, an insert, and a disconnect.

If you can't pull the data, because you have a one way trust relationship between the servers, the work around is to construct the entire table as a giant T-SQL statement and run it all at once.

DECLARE @xml XML

SET @xml = (
        SELECT 'insert Remote_Table values (' + '''' + isnull(first_col, 'NULL') + ''',' +
            -- repeat for each col
            '''' + isnull(last_col, 'NULL') + '''' + ');'
        FROM Local_Table
        FOR XML path('')
        ) --This concatenates all the rows into a single xml object, the empty path keeps it from having <colname> </colname> wrapped arround each value

DECLARE @sql AS VARCHAR(max)

SET @sql = 'set nocount on;' + cast(@xml AS VARCHAR(max)) + 'set nocount off;' --Converts XML back to a long string

EXEC ('use RemoteDb;' + @sql) AT RemoteServer

Answer 2

It seems like it's much faster to pull data from a linked server than to push data to a linked server: Which one is more efficient: select from linked server or insert into linked server?

Update: My own, recent experience confirms this. Pull if possible -- it will be much, much faster.

Try this on the other server:

INSERT INTO Local_Table
SELECT * FROM RemoteServer.RemoteDb.Remote_Table

Answer 3

The Import/Export wizard will be essentially doing this as a bulk insert, where as your code is not.

Assuming that you have a Clustered Index on the remote table, make sure that you have the same Clustered index on the local table, set Trace flag 610 globally on your remote server and make sure remote is in Simple or bulk logged recovery mode.

If you're remote table is a Heap (which will speed things up anyway), make sure your remote database is in simple or bulk logged mode change your code to read as follows:

INSERT INTO RemoteServer.RemoteDb..Remote_Table WITH(TABLOCK)
SELECT * FROM Local_Table WITH (nolock)