create procedure UselessLoop @I int as declare @D datetime = getdate() while @I > 0 set @I -= 1 print datediff(millisecond,@D,getdate())
exec UselessLoop 100000@H_502_5@
Server Milliseconds local 53 nearby 63 faraway 660
exec UselessLoop 1000000@H_502_5@
Server Milliseconds local 546 nearby 640 faraway 6183
使用SSMS从不同计算机对同一服务器执行测试.本地从服务器执行,附近是在同一个本地网络上,远处是500公里外的另一个办公室,用1千兆光纤连接.@H_502_5@
sql Server和客户端之间显然存在一些直接依赖于执行的语句数量的通信.@H_502_5@
我使用Wireshark来查看传输的内容,我不能说我理解那么多,但它是一个tcp.stream,在22740个数据包中交换总共26 MB.@H_502_5@
create function dbo.UDFUselessLoop(@I int) returns int as begin declare @D datetime = getdate() while @I > 0 set @I -= 1 return datediff(millisecond,getdate()) end
print dbo.UDFUselessLoop(1000000)@H_502_5@
无论从何处执行,它都会在406毫秒内执行.看起来循环中没有与客户端的通信.@H_502_5@
解决方法
There is obvIoUsly some communication going on between sql Server and the client that directly is dependent on the number of statements executed.@H_502_5@
就在这里.默认情况下,sql Server在存储过程中的每个语句之后发送一个TDS DONE_IN_PROC
message.该消息将完成的语句的状态和行计数信息传递给客户端.@H_502_5@
您可以使用T-sql命令禁止发送这些消息:@H_502_5@
SET NOCOUNT ON;
以下是此命令从the Books Online entry开始的摘录(我的重点):@H_502_5@
For stored procedures that contain several statements that do not return much actual data,or for procedures that contain Transact-sql loops,setting SET NOCOUNT to ON can provide a significant performance boost,because network traffic is greatly reduced.@H_502_5@