Interaction with the selected server

Question

Suppose I have a table in my local directory that has Local_Table , and I have another server and another db and a table that is Remote_Table (the table structures are the same).

Local_Table has data, Remote_Table does not. I want to transfer data from Local_Table to Remote_Table using this query:

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

But the performance is pretty slow.

However, when I use the SQL Server Import-Export Wizard, the transfer is very fast.

What am I doing wrong? Why is this happening fast with the import-export wizard and slowed down with the insert-select statement? Any ideas?

Answer 1

The fastest way is to pull out the data, rather than pushing it. When tables are moved, each row requires connecting, inserting, and disconnecting.

If you cannot pull the data because you have a one-way trust between the servers, the work around is to build the whole table like a giant T-SQL statement and run it right away.

 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 that it is much faster to retrieve data from a linked server than to transfer data to a linked server: Which one is more efficient: select from a linked server or paste it onto a linked server?

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

Try this on another server:

 INSERT INTO Local_Table SELECT * FROM RemoteServer.RemoteDb.Remote_Table 

Answer 3

The Import / Export Wizard will essentially do this as a bulk insert, where your code is not.

Assuming you have a clustered pointer in the remote table, make sure you have the same clustered index in the local table, check trace 610 globally on your remote server and make sure that the remote computer is in simple or bulk recovery log mode.

If you are a remote table, this is a bunch (which will speed things up anyway), make sure your remote database is in a simple or bulk log, change your code as follows:

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

Answer 4

The reason that it is so slow to insert into the remote table from the local table is because it inserts a row, checks its insertion and then inserts the next row, checks that it is inserted, etc.

I don’t know if you understood it or not, but here’s how I solved this problem using linked servers.

Firstly, I have LocalDB.dbo.Table with multiple columns:

 IDColumn (int, PK, Auto Increment) TextColumn (varchar(30)) IntColumn (int) 

And I have RemoteDB.dbo.Table, which is almost the same:

 IDColumn (int) TextColumn (varchar(30)) IntColumn (int) 

The main difference is that the remote IDColumn is not configured as an identifier column, so I can embed it in it.

Then I set the trigger on the remote table, which happens when deleting

 Create Trigger Table_Del On Table After Delete AS Begin Set NOCOUNT ON; Insert Into Table (IDColumn, TextColumn, IntColumn) Select IDColumn, TextColumn, IntColumn from MainServer.LocalDB.dbo.table L Where not exists (Select * from Table R WHere L.IDColumn = R.IDColumn) END 

Then, when I want to do the insertion, I do it like this on the local server:

 Insert Into LocalDB.dbo.Table (TextColumn, IntColumn) Values ('textvalue', 123); Delete From RemoteServer.RemoteDB.dbo.Table Where IDColumn = 0; --And if I want to clean the table out and make sure it has all the most up to date data: Delete From RemoteServer.RemoteDB.dbo.Table 

By running a remote server to pull data from the local server and then paste, I was able to complete a task that took 30 minutes to insert 1258 rows into a task that took 8 seconds to do the same paste.

This requires a connected connection to the server on both sides, but after that it works very well.

Update:
Therefore, in the last few years, I made some changes and moved away from the delete trigger as a way to synchronize the deleted table.

Instead, I have a stored procedure on a remote server that has all the steps to pull data from a local server:

 CREATE PROCEDURE [dbo].[UpdateTable] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here --Fill Temp table Insert Into WebFileNamesTemp Select * From MAINSERVER.LocalDB.dbo.WebFileNames --Fill normal table from temp table Delete From WebFileNames Insert Into WebFileNames Select * From WebFileNamesTemp --empty temp table Delete From WebFileNamesTemp END 

And on the local server, I have a scheduled task that does some processing in local tables, and then starts the update through a stored procedure:

 EXEC sp_serveroption @server='REMOTESERVER', @optname='rpc', @optvalue='true' EXEC sp_serveroption @server='REMOTESERVER', @optname='rpc out', @optvalue='true' EXEC REMOTESERVER.RemoteDB.dbo.UpdateTable EXEC sp_serveroption @server='REMOTESERVER', @optname='rpc', @optvalue='false' EXEC sp_serveroption @server='REMOTESERVER', @optname='rpc out', @optvalue='false' 

Answer 5

If you need to transfer data from a source to a target (for example, for a firewall or other permissions), you can do the following:

In the source database, convert the recordset into a single XML line (i.e. multiple rows and columns combined into a single XML line). Then move this XML as one line (like varchar (max), since XML is not allowed across linked databases in SQL Server).

  DECLARE @xml XML SET @xml = (select * from SourceTable FOR XML path('row')) Insert into TempTargetTable values (cast(@xml AS VARCHAR(max))) 

In the target database, draw varchar (max) as XML and then use XML parsing to turn this single row and column into a regular record set.

 DECLARE @X XML = (select '<toplevel>' + ImportString + '</toplevel>' from TempTargetTable) DECLARE @iX INT EXEC sp_xml_preparedocument @ix output, @x insert into TargetTable SELECT [col1], [col2] FROM OPENXML(@iX, '//row', 2) WITH ([col1] [int], [col2] [varchar](128) ) EXEC sp_xml_removedocument @iX