SQL server查询性能是否取决于对客户端的网络速度?

My test query runs faster when I do a Remote Desktop connection to the SQL Server box, and open Management Studio from there, than when I connect using Management Studio from remote workstation. The question is: WHY?!

当我使用远程桌面连接到SQL Server box并从那里打开Management Studio时，我的测试查询运行得比使用远程工作站上的Management Studio时要快。现在的问题是:为什么? !

My query does not return any data back. Here's the exact text of my query:

我的查询不会返回任何数据。以下是我的提问的具体内容:

set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls off
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed 


exec dbo.Test

Where Test is defined as follows:

其中测试定义如下:

CREATE PROCEDURE dbo.Test
AS
BEGIN  
 declare @cnt int = 0  
 declare @d as datetime  
 set @d = dateadd(ss,5,GETDATE());  

 WHILE GETDATE() < @d
 BEGIN  
  SET @cnt = @cnt + 1  
 END

 print @cnt  
END

Both queries take 5 seconds to execute, but the local run returns 7 times bigger value of cnt than the remote run.

两个查询执行时间都为5秒，但是本地运行返回的cnt值是远程运行的7倍。

Looking at profiler, LPC protocol is used locally, while TCP/IP is used for remote connection. I tried named pipes and it is even slower. I thought if query execution does not involve returning data to the client it should not depend on the network connection or the protocol used but apparently it does.

查看分析器，本地使用LPC协议，远程连接使用TCP/IP。我尝试了命名管道，它甚至更慢。我认为，如果查询执行不涉及向客户端返回数据，那么它不应该依赖于所使用的网络连接或协议，但显然它确实需要。

Any ideas why? Can you validate my results?

任何想法为什么?你能验证我的结果吗?

2 个解决方案

#1

Every time the SET command is called there is feedback sent to the client.

每次调用SET命令时，都会向客户端发送反馈。

If you change the code as shown below performance will be a lot better (even local). I get about 9 million loops without and 14 million with the SET NOCOUNT ON. As a general rule you should always switch SET NOCOUNT to on as first statement of a stored procedure.

如果您更改如下所示的代码，性能将会更好(甚至是本地的)。我得到了大约900万个循环没有和1400万个集合NOCOUNT ON。作为一般规则，您应该始终将SET NOCOUNT设置为on作为存储过程的第一个语句。

CREATE PROCEDURE dbo.Test
AS
BEGIN  
 SET NOCOUNT ON
 declare @cnt int = 0  
 declare @d as datetime  
 set @d = dateadd(ss,5,GETDATE());  

 WHILE GETDATE() < @d
 BEGIN  
  SET @cnt = @cnt + 1  
 END

 print @cnt  
END

The SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. More information about DONE_IN_PROC can be found on MSDN

SET NOCOUNT消除了为存储过程中的每个语句向客户端发送DONE_IN_PROC消息。关于DONE_IN_PROC的更多信息可以在MSDN上找到

#2

Even when there is no data to be returned, Management Studio has quite a "conversation" with the SQL server instance when running a query. Running SQLMS as an app on the server will virtually always result in faster execution times, because when you remote in to terminal services (RDP), the network is only used for GUI messages. When you're running an instance of SQLMS locally and connecting it to a remote server, that "conversation" between client and server, which involves more data than UI messages, takes place over the LAN, which is guaranteed to be slower than LPC.

即使不返回数据，Management Studio在运行查询时也会与SQL server实例进行相当“对话”。将SQLMS作为应用程序运行在服务器上几乎总是会导致更快的执行时间，因为当您远程访问终端服务(RDP)时，网络仅用于GUI消息。当您在本地运行SQLMS实例并将其连接到远程服务器时，客户机和服务器之间的“对话”(涉及的数据比UI消息多)将在LAN上进行，而LAN保证比LPC慢。

#1