连接到关联服务器时查询速度缓慢。

Question

我有一个查询： UPDATE linkeddb...table SET field1 = 'Y' WHERE column1 = '1234' 这个操作需要23秒才能选择和更新一行。

但是，如果我使用openquery（我不想使用），那么只需要半秒钟。

我不想使用openquery的原因是我想安全地向我的查询中添加参数，以防止SQL注入攻击。

有人知道它运行得如此缓慢的原因吗？

- Jamie Taylor

---

查询执行计划有什么线索吗？或者您可以设置SQL Profiler来监视数据库并查看openquery的不同之处。 - Rup

Answer 1

这里有一个替代方案。在远程服务器上创建一个存储过程来执行更新，然后从本地实例调用该存储过程。

/* On remote server */
create procedure UpdateTable
    @field1 char(1),
    @column1 varchar(50)
as
    update table
        set field1 = @field1
        where column1 = @column1
go

/* On local server */
exec linkeddb...UpdateTable @field1 = 'Y', @column1 = '1234'

- Joe Stefanelli

---

@Joe - 存储过程基本上就是数据库中的一个函数/方法吗？ - orokusaki

@orokusaki：是的，你可以这样想。 - Joe Stefanelli

@Joe 这样做只会稍微快一点，因为省去了解析和编译时间。@orokusaki 它仍然比你的方法更好，因为它让数据库自己保护自己免受 SQL 注入攻击。您还可以设置权限，使用户无法直接更新表，只能通过存储过程进行操作。 - stevenrcfox

@Overflow：实际上，我期望会有更显著的改进，因为执行更新的工作负载被转移到了远程服务器的SQL引擎。在OP当前的版本中，本地SQL引擎试图更新远程资源。 - Joe Stefanelli

@Joe，这种行为是依赖于特定的关系型数据库管理系统或驱动程序吗？我运行了两个版本，使用Wireshark在网络上查看数据，结果都是查询发送到服务器，并返回数据。如果本地SQL引擎运行查询，我应该期望看到什么？我原本想象要么是将数据获取到本地缓存并批量更新数据，要么是逐行检索和更新。 - stevenrcfox

这将有助于减轻潜在的排序问题。此外，您只需要为适当的登录名授予一个SP的EXEC权限，而不是直接表权限。 - Cade Roux

Answer 2

如果你想知道原因，这里有一个可能性来自Linchi Shea的博客:

为了在使用链接服务器上的表时创建最佳查询计划，查询处理器必须从链接服务器获取数据分布统计信息。对于任何列权限受限的用户，可能没有足够的权限来获取所有有用的统计信息，可能会收到不太有效的查询计划并体验较差的性能。如果链接服务器是SQL Server实例，则要获取所有可用的统计信息，用户必须拥有该表或是sysadmin固定服务器角色、db_owner固定数据库角色或db_ddladmin固定数据库角色的成员。

（由于Linchi的帖子，这一澄清已经被添加到最新的BooksOnline SQL文档中）。

换句话说，如果链接服务器设置了具有有限权限的用户，则SQL无法为表检索准确的统计信息，并可能选择执行查询的较差方法，包括检索所有行。

这里有一个关于链接服务器查询性能的相关SO问题。他们的结论是：使用OpenQuery可以获得最佳性能。

更新：Linchi的博客中还有一些关于链接服务器性能的其他优秀的帖子。

- BradC

Answer 3

column1 是否为主键？可能不是。尝试使用主键选择记录以进行更新（where PK_field=xxx），否则（有时候？）将读取所有记录以查找要更新的记录的主键。

- Arvo

---

我相信column1是主键，但在SQL查询分析器中查看执行计划时，它显示远程扫描需要最长时间，因此似乎正在遍历所有40,000条记录。 - Jamie Taylor

如果您的PK是(n)varchar，那么您可能会遇到一些排序问题（我的意思是SQL不使用这样的索引，因为它不知道排序或其他原因）。不过，我没有非整数PK字段的经验。 - Arvo

@Jamie，我赞同Arvo的排序理论可能存在问题。 - stevenrcfox

Answer 4

column1是一个varchar字段吗？这就是为什么你用单引号将值1234括起来的原因吗？还是这只是你问题中的一个笔误？

- Tim

---

如果我在查询分析器中运行查询而没有使用引号，我会收到一个错误提示，我认为column1是一个varchar字段。 - Jamie Taylor