使用变量与文字时，远程查询速度较慢 [英] Remote Query is slow when using variables vs literal

问题描述

我已到处寻找此情况，但除了不想使用的动态SQL外，找不到解决方案。

以下是我要在服务器2上更新的表：

(Stuff Id UNIQUEIDENTIFIER
, stuffname NVARCHAR(64))

我需要从服务器%1更新它。

所以我一直在尝试：

DECLARE @newstuff nvarchar(64)

SELECT @newstuff = 'new stuff'

UPDATE [server2].database2.dbo.Stuff
SET stuffname=@newstuff
WHERE stuffId='4893CD93-08B3-4981-851B-5DC972288290'

这需要11秒。下一次使用文字的运行时间不到1秒

UPDATE [server2].database2.dbo.Stuff
SET stuffname='new stuff'
WHERE stuffId='4893CD93-08B3-4981-851B-5DC972288290'

我已经比较了实际的执行计划。速度慢的是进行远程扫描，这需要100%的成本，外加5个其他步骤(过滤、表假脱机、计算标量、远程更新、更新)。最快的那个只执行更新和远程查询步骤。我需要使用变量，因此需要一种强制它远程执行整个查询的方法。

我已尝试使用选项(重新编译)，但server1使用的是SQL Server 2005。服务器2正在使用SQL Server 2012。在没有严重问题的情况下，我根本无法更改server2上的数据库结构。我没有任何身份验证问题。我已在更新表时尝试对表进行别名处理。

我也尝试过使用Openquery。当我将id过滤放在查询字符串中时，它又回到了1秒以下：

UPDATE OPENQUERY([server2], 'select stuffname, stuffid from database2.dbo.stufftable where contactid=''4CA1D489-9221-E511-A441-005056C00008''')
SET stuffname = @newstuff

但是我还需要该id是一个变量，并且打开的查询不接受变量(https://msdn.microsoft.com/en-CA/library/ms188427.aspx)。我尝试在查询外部使用id为过滤的openquery运行openquery，但运行时间为4秒。比11好，但不是很好：

UPDATE OPENQUERY([server2],'select stuffname, stuffid from database2.dbo.stufftable')
set stuffname=@newstuff
where contactid='4CA1D489-9221-E511-A441-005056C00008'

当然，我使用exec(@sql)运行openquery，但我真的不想这样做。我可以使用文字以这种方式执行整个UPDATE语句，甚至不使用OPENQUERY并获得同样的结果。

有没有办法在不使用exec(@sql)的情况下修复此性能？

推荐答案

您可以在远程端使用带参数的动态sql sp_ecutesql。

declare @SQL nvarchar(max);

set @SQL = 'UPDATE database2.dbo.Stuff
            SET stuffname=@newstuff
            WHERE stuffId=''4893CD93-08B3-4981-851B-5DC972288290'''

exec [server2].master.dbo.sp_executesql @SQL, N'@newstuff nvarchar(64)', @newstuff

这篇关于使用变量与文字时，远程查询速度较慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！