连接到链接服务器时慢查询 [英] Slow query when connecting to linked server

问题描述

我有此查询

更新linkeddb ...表中设置字段1 ='Y'WHERE列1 ='1234'

这需要23秒的选择和更新一排

This takes 23 seconds to select and update one row

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

But if I use openquery (which I don't want to) then it only takes half a second.

我不希望使用OPENQUERY的原因是这样我就可以安全参数添加到我的查询，并从SQL注入安全的。

The reason I don't want to use openquery is so I can add parameters to my query securely and be safe from SQL injections.

有谁知道的任何理由为它如此运行缓慢？

Does anyone know of any reason for it to be running so slowly?

推荐答案

下面有一个想法作为替代。在远程服务器上创建一个存储过程来执行更新，然后调用从本地实例的过程。

Here's a thought as an alternative. Create a stored procedure on the remote server to perform the update and then call that procedure from your local instance.

/* 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'

这篇关于连接到链接服务器时慢查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！