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

Question

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.

Answer 1

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

Answer 2

How are you measuring efficiency? Which one will be faster? Which one will consume fewer resources on the target? on the source? How many rows and what kind of data types are the columns in these rows? Are you sure you can execute a TVF through a linked server (is the target SQL 2008 or later?)? How are you ensuring a 1:1 migration of this data if you're pulling from a TVF?

With those questions out of the way ...

Update 1

It sounds like you're looking for ETL (Extract-Transform-Load). I'd recommend SSIS (SQL Server Integration Services) with which you can pull the data from the source, apply the transformations that you need, and then load those into your target. This sounds like it'd be a pretty straightforward package (depending on the transformations).

---

Conventional wisdom states that the linked server approach will go out to the link, pull the data to the local server, and then apply any logic (filters, joins, etc.) on the local server. There is some overhead to fetch the data on the linked server, but the majority of the processing will be handled locally.

The OPENQUERY method will put the processing on the remote server and the "filtered results" will be received by the local server.

It seems like even if you could execute a TVF through a linked server, you'd be getting the worst of both worlds, processing remotely and processing locally (assuming you had additional logic to apply upon the set).

Depending on how you decide to move forward, I'd also look into OPENQUERY as a means to bulk import/export data.

Having said all of that ...

If both the source and the target at SQL Server (and the target is not a lower version), why not do a backup and restore of the data? This would be a true data migration. Here's some code for you.

BACKUP DATABASE <DatabaseName, sysname, DatabaseName>
TO DISK=N'<backup_location, varchar, BackupLocation>.bak'
WITH INIT, FORMAT, COMPRESSION, COPY_ONLY

RESTORE DATABASE <NewDatabaseName, sysname, NewDatabaseName>
FROM DISK = N'<backup_location, varchar, BackupLocation>\
    <DatabaseName, sysname, DatabaseName>.bak'
WITH 
    MOVE '<DataFileName, sysname, DataFileName>' TO '<DataMDFPath, nvarchar(600), DataMDFPath>',
    MOVE '<LogFilePath, sysname, LogFilePath>' TO '<LogLDFPath, nvarchar(600), LogLDFPath>',
    REPLACE;

You can refer to this answer on how to use templates in SSMS.