Which one is more efficient: select from linked server or insert into linked server?

Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient?

Executing in source server:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

Or executing in target server:

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

Which one will be faster and consume fewer resourcers in total (both source and target server)? Both servers are SQL Server 2005.

Suppose I have to export data from one server to another.

Best is to use

• IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS

• IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN

TO move data, depending on the amount/size of data and n/w bandwidth, Linked server will kill the performance.


Executing in source server Or executing in target server - Which one will be faster and consume fewer resourcers in total (both source and target server)?

-- Executing in source server:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

This is called PUSHING Data as you are executing the query on source server and pushing the data into destination server. This will be expensive operation.

--- executing in target server

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

This is called PULLING Data as you are executing the query on destination server and pulling data from source server. This will be a lot faster and less resource intensive compared to the former one (depending on how much data is being pulled).

In the case of the pull method, using SQL Profiler you will see that a single SQL statement is executed across the linked server (source server), and the resultset is pulled over from source server to destination server which is a huge performance gain over PUSH method.

Another point to be noted is :

Between Linked server (4 part naming convention used servername.databasename.schema.tablename a.k.a Distributed Queries) and OPENQUERY, generally OPENQUERY will be fast. Why ?

For Linked Server - Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

For OPENQUERY - Executes the specified pass-through query on the specified linked server. SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source . Hence, SQL won’t apply any kind of logic on the query and won’t try to estimate what that query would do, it would simply pass the specified query as it is to the target linked server. Open queries are useful when you are not referencing multiple servers in one query. It’s generally fast as SQL does not break it into multiple operations and does not perform any local action on the output received.

Excellent reading references :

• Linked servers and performance impact: Direction matters!

• Distributed Queries

• OPENQUERY

StackExchange Database Administrators Q#46289, answer score: 32