使用链接服务器的SQL更新非常慢 [英] Very Slow SQL Update using a Linked Server

问题描述

我在服务器(ServerA)上运行了一个sql脚本 该服务器具有一个链接服务器设置(ServerB)-它位于数据中心的外部.

I have a sql script running on a server (ServerA) This server, has a linked server set up (ServerB) - this is located off site in a datacenter.

此查询的工作相对较为轻松:

This query works relatively speeidily:

SELECT OrderID
FROM [ServerB].[DBName].[dbo].[MyTable]
WHERE Transferred = 0

但是，使用此查询更新同一张表时:

However, when updating the same table using this query:

UPDATE [ServerB].[DBName].[dbo].[MyTable]
SET Transferred = 1

要花费超过1分钟的时间(即使只有1列，其中Transferred = 0)

It takes > 1 minute to complete (even if there's only 1 column where Transferred = 0)

是否有任何理由会如此缓慢地行动? 我应该在MyTable上为已转移"列建立索引吗?

Is there any reason this would be acting so slowly? Should I have an index on MyTable for the "Transferred" column?

推荐答案

如果您(我的意思是SQL Server)无法使用远程端的索引来选择记录，则这种远程更新实际上会读取所有记录(主键和其他所需字段) )从本地进行更新，然后将更新后的记录发送回去.如果您的链接速度很慢(例如10Mbit/s或更低)，则这种情况将花费大量时间.

If you (I mean SQL server) cannot use index on remote side to select records, such remote update in fact reads all records (primary key and other needed fields) from remote side, updates these locally and sends updated records back. If your link is slow (say 10Mbit/s or less), then this scenario takes lot of time.

我在远程端使用了存储过程-这样，您只应远程调用该过程(带有一组可选参数).如果您的可更新子集很小，那么适当的索引也可能会有所帮助-但存储过程通常会更快.

I've used stored procedure on remote side - this way you should only call that procedure remotely (with set of optional parameters). If your updateable subset is small, then proper indexes may help too - but stored procedure is usually faster.

这篇关于使用链接服务器的SQL更新非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！