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