联接服务器插入-选择性能

Question

假设我本地有一张名为Local_Table的表，另外我还有一台服务器和另一个数据库和表，名为Remote_Table（表结构相同）。 Local_Table中有数据，而Remote_Table中没有。我想使用以下查询将数据从Local_Table传输到Remote_Table：

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

但是性能相当慢。

然而，当我使用SQL Server导入导出向导时，传输速度非常快。

我做错了什么？为什么使用Insert-Select语句很慢，而使用导入导出向导很快？有任何想法吗？

- Mehmet

Answer 1

比起推送数据，拉取数据是最快的方式。当表被推送时，每一行都需要一个连接、一个插入和一个断开。

如果你不能拉取数据，因为服务器之间存在单向信任关系，则解决方法是构建整个表作为一个巨大的 T-SQL 语句，并一次性运行它。

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

- Tyler W. Cox

---

每一行。哇。这就解释了每秒不到100行的插入速度问题。使用“select * into LocalTable from RemoteServer”将10万行数据导入本地表只需要一两秒钟。但是“insert into RemoteServer from LocalTable”的运行时间已经过了一分钟，只插入了1000行数据。简直疯了。谁设计了这么糟糕的系统？ - Triynko

Answer 2

似乎从链接服务器获取数据比将数据推送到链接服务器更快：哪个更有效率：从链接服务器选择还是插入到链接服务器中？ 更新：我的最近经验证实了这一点。如果可能的话，请拉取数据--速度会更快。

在另一个服务器上尝试此操作：

INSERT INTO Local_Table
SELECT * FROM RemoteServer.RemoteDb.Remote_Table

- Christian Davén

Answer 3

如果必须从源推送数据到目标（例如出于防火墙或其他权限原因），您可以执行以下操作： 在源数据库中，将记录集转换为单个XML字符串（即，将多行和列组合成单个XML字符串）。 然后将该XML作为单个行（作为varchar（max），因为在SQL Server中不允许通过链接的数据库传输XML）进行推送。

    DECLARE @xml XML

    SET @xml = (select * from SourceTable FOR XML path('row'))

    Insert into TempTargetTable values (cast(@xml AS VARCHAR(max)))

在目标数据库中，将varchar(max)转换为XML，然后使用XML解析将该单行和列转换回普通记录集。

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

- Greg Atkinson

Answer 4

从本地表插入远程表速度缓慢的原因是它会插入一行，检查它是否插入成功，然后再插入下一行，继续检查，如此循环。不知道您是否已经解决了这个问题，但我用链接服务器解决了这个问题。首先，我有一个LocalDB.dbo.Table带有几列：

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

我有一个几乎相同的RemoteDB.dbo.Table：

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

主要区别在于远程IDColumn没有设置为ID列，这样我就可以向其中插入数据。

然后我在远程表上设置了一个触发器，它会在删除时触发。

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

当我想要进行插入操作时，我会从本地服务器执行以下操作：

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

通过触发远程服务器从本地服务器提取数据并进行插入，我成功将需要30分钟才能插入1258行数据的工作转换为只需8秒钟即可完成相同的插入工作。这确实需要在两侧都建立链接服务器连接，但是设置好之后效果还是不错的。

更新： 在过去的几年中，我已经做出了一些改变，并且不再使用删除触发器来同步远程表。 相反，我在远程服务器上有一个存储过程，其中包含从本地服务器提取数据的所有步骤：

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

在本地服务器上，我有一个定时任务对本地表进行处理，然后通过存储过程触发更新：

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'

- AndyD273

---

这似乎是针对您的使用情况的非常特定的解决方案，而不是一般情况下的最佳选择 - 如果我在您转向更大更好的项目后进行真正的删除，那该怎么办？然后突然发现我比删除前有更多的行，因为您运行了同步？这将是一个噩梦，因为要找到给定删除触发器的原因。 - Steve Radich-BitShop.com

这个解决方案主要是为了确保远程服务器与本地服务器匹配，并且可以从本地服务器上的作业触发。它并不意味着是双向的。实质上，它表达的是在我之后回答的两个人所说的相同的事情。推送很慢，拉取很快。在过去的三年中，我已经放弃了删除触发器，因为我找到了如何使用远程存储过程的方法。这避免了意外删除的问题。如果远程服务器需要最新的信息，我会在运行时从本地拉取一两行，但大多数东西并没有那么频繁地更改。 - AndyD273

Answer 5

导入/导出向导将作为批量插入执行此操作，而您的代码则不是。

假设您在远程表上有一个聚集索引，请确保在本地表上具有相同的聚集索引，在远程服务器上全局设置跟踪标志610，并确保远程处于简单或批量记录恢复模式。

如果您的远程表是堆（这将加快速度），请确保您的远程数据库处于简单或批量记录模式，并将您的代码更改为以下内容：

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

- Pete Carter

---

这个回答实际上非常正确，但它并不适用于这个问题/主题 - 然而，任何阅读它的人都应该知道这是一个好建议，因此我对这个答案进行了点赞，但是加上了这个评论。 - Steve Radich-BitShop.com

Answer 6

我找到了一个解决方法。由于我不是很喜欢GUI工具（如SSIS），所以我重新利用了一个bcp脚本将表加载到csv中，反之亦然。是的，这种支持大批量操作文件而不是表的情况很奇怪。请随意编辑以下脚本，以适应您的需求：

exec xp_cmdshell 'bcp "select * from YourLocalTable" queryout C:\CSVFolder\Load.csv -w -T -S .' 
exec xp_cmdshell 'bcp YourAzureDBName.dbo.YourAzureTable in C:\CSVFolder\Load.csv -S yourdb.database.windows.net -U [e-mail] -P yourpass -q -w' 

优点：

• 无需每次定义表结构。
• 我进行了测试，它比直接通过LinkedServer插入数据的方式快得多。
• 比XML更易于管理（XML仅限于varchar（max）长度）。
• 无需额外的抽象布局（例如SSIS等工具）。

缺点：

• 需要通过xp_cmdshell接口使用外部工具bcp。
• 在导入/导出csv后，表属性将丢失（例如数据类型、空值、长度、值内分隔符等）。

- Danylo Korostil

---

太棒了！ - Becker