假设我必须将数据从一台服务器导出到另一台(通过链接的服务器)。哪条语句会更有效?

在源服务器中执行:

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


或在目标服务器中执行:

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


哪一个会更快并且总共消耗更少的资源管理器(源服务器和目标服务器)?两台服务器都是SQL Server2005。

#1 楼


假设我必须将数据从一台服务器导出到另一台服务器。


最好使用


如果要使用所有数据备份/还原; BCP OUT和BCP IN或SSIS
如果要数据子集(仅某些表),请使用SSIS或BCP OUT和BCP IN

根据数据量/大小移动数据和n / w带宽,链接服务器会降低性能。


在源服务器中执行或在目标服务器中执行-哪一个会更快,并且总共消耗更少的资源(资源和目标服务器)?


-在源服务器上执行:

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


当您在源服务器上执行查询时,这称为PUSHING Data。并将数据推送到目标服务器。这将是昂贵的操作。

---在目标服务器中执行

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


当您在以下位置执行查询时,这称为PULLING Data目标服务器并从源服务器提取数据。与前一种方法相比(取决于要拉取的数据量),这将更快,更节省资源。

对于pull方法,使用SQL Profiler,您会看到在链接服务器(源服务器)上执行单个SQL语句,结果集从源服务器转移到目标服务器,这比PUSH方法有很大的性能提升。

要注意的另一点是:

在链接服务器(4部分命名约定,使用servername.databasename.schema.tablename aka分布式查询)和OPENQUERY之间,通常OPENQUERY会很快。为什么?

对于链接服务器-查询优化器通过查看查询术语来创建执行计划,并将其分为远程和本地查询。本地查询是在本地执行的,而远程查询的数据是从远程服务器收集,本地清理,组合在一起并作为单个记录集呈现给最终用户的。

对于OPENQUERY-在指定的链接服务器上执行指定的传递查询。 SQL Server将直通查询作为未解释的查询字符串发送到OLE DB数据源。因此,SQL不会对查询应用任何逻辑,也不会尝试估计该查询将执行的操作,它只会将指定的查询直接传递给目标链接服务器。当您不在一个查询中引用多个服务器时,打开查询很有用。由于SQL不会将其分解为多个操作并且不会对接收到的输出执行任何本地操作,因此通常速度很快。

优秀的阅读参考资料:


链接服务器和性能影响:方向很重要!
分布式查询

OPENQUERY


#2 楼

您如何衡量效率?哪一个会更快?哪一个将消耗较少的目标资源?在源头上?这些行中的列有多少行和什么样的数据类型?您确定可以通过链接服务器执行TVF(目标SQL 2008或更高版本吗?)?如果您要从TVF中提取数据,如何确保这些数据按1:1迁移?

这些问题让您望而却步...

更新1

听起来您正在寻找ETL(Extract-Transform-Load)。我建议使用SSIS(SQL Server集成服务),您可以使用它从源中提取数据,应用所需的转换,然后将其加载到目标中。听起来这将是一个非常简单的程序包(取决于转换)。


传统观点认为,链接服务器方法将用于链接,将数据拉至本地服务器,然后在本地服务器上应用任何逻辑(过滤器,联接等)。在链接服务器上获取数据会有一些开销,但是大部分处理将在本地进行。

OPENQUERY方法将把处理放在远程服务器上,本地服务器将接收“过滤的结果”。

即使您可以通过链接的服务器执行TVF,您也会陷入两全其美的境地:远程处理和本地处理(假设您有其他逻辑可应用于该集合)。

根据您决定前进的方式,我还将研究OPENQUERY作为批量导入/导出数据的一种方式。

说了这么多...

如果SQL Server的源和目标(并且目标不是较低版本),为什么不做备份并恢复数据?这将是一次真正的数据迁移。这是为您提供的一些代码。

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;


您可以参考此答案以了解如何在SSMS中使用模板。