链接服务器插入选择性能 [英] Linked Server Insert-Select Performance
问题描述
假设我的本地表为Local_Table
,并且我有另一台服务器,另一个数据库和表为Remote_Table
(表结构相同).
Assume that I have a table on my local which is Local_Table
and I have another server and another db and table, which is Remote_Table
(table structures are the same).
Local_Table
有数据,Remote_Table
没有.我想通过以下查询将数据从Local_Table
传输到Remote_Table
:
Local_Table
has data, Remote_Table
doesn't. I want to transfer data from Local_Table
to Remote_Table
with this query:
Insert into RemoteServer.RemoteDb..Remote_Table
select * from Local_Table (nolock)
但是性能却很慢.
但是,当我使用SQL Server导入和导出向导时,传输确实非常快.
However, when I use SQL Server import-export wizard, transfer is really fast.
我做错了什么?为什么使用Import-Export向导很快,而使用insert-select语句却慢呢?有什么想法吗?
What am I doing wrong? Why is it fast with Import-Export wizard and slow with insert-select statement? Any ideas?
推荐答案
最快的方法是提取数据而不是推送数据.推入表后,每一行都需要一个连接,一个插入和一个断开连接.
The fastest way is to pull the data rather than push it. When the tables are pushed, every row requires a connection, an insert, and a disconnect.
如果由于服务器之间具有单向信任关系而无法提取数据,则解决方法是将整个表构造为巨型T-SQL语句,然后立即运行所有表.
If you can't pull the data, because you have a one way trust relationship between the servers, the work around is to construct the entire table as a giant T-SQL statement and run it all at once.
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
这篇关于链接服务器插入选择性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!