使用NOCOUNT提高过程性能

Question

我希望改进某个过程的性能，我想从插入SET NOCOUNT ON开始。

我读过几篇关于这个问题的文章：

亚伦·伯特兰

在提高服务器性能的基础上设置NOCOUNt

但我并不真正理解的是，这是每个过程都需要一次，还是每次“开始/结束”时都需要插入

例如，在下面的过程中-我是否应该插入SET NOCOUNT ON右转，最后一个变量设置为：set @PrintInfo = 'No Trip Number...

或者，我是否需要在过程中的每一个“开始”之后插入SET NOCOUNT ON：

Create procedure [dbo].SSIS_UpdateDriveResults
(
@RSADriveID nvarchar (50),
@ProcedureID int,
@Registered int,
@Performed  int,
...
)
as 

set @ResultError = 0
set @ResultMessage = ''

declare @Id int
declare @Name varchar(64)
declare @DriveId int
declare @ErrorMessage nvarchar(255)
...

Set @ExternalIDs = cast(@RSADriveID as nvarchar(50))
set @IsFixedSite = 'N'
set @CurrentDate = getdate()
set @UpdateWho = -1
set @PrintInfo = ' No Trip Number   ' + convert(varchar(8), @RSADriveID, 1) 

select @UpdateWho = personid from db_name.[dbo].peoplelogindetail where loginid = 'RMADMIN'

if @UpdateWho <= 0
begin
set @ResultError = 1
set @ResultMessage = 'ERROR:Unable to locate employee with login RMADMIN for inserts. ' + @PrintInfo
print @ResultMessage
return
end


--Get the account or fixed site    
select top 1 @Id = a.accountid, @Name = a.name 
from db_name.[dbo].accounts a 
where a.accountid in
    (Select accountid from db_name.[dbo].drivemaster where deleted = 0 and statusid not in (5) and driveid in
        (select driveid from db_name.[dbo].DriveShiftDetail where ShiftID = (@RSADriveID))) --ShiftID

if(@Id is null or @Id <=0 )
begin
-- See if this is a fixed site
select @Id = dm.centerid, @Name = cd.desclong 
    from db_name.[dbo].drivemaster dm 
    join db_name.[dbo].centerdetail cd on dm.centerid = cd.centerid 
    where dm.deleted = 0 and dm.statusid not in (5) and dm.driveid in
        (select driveid from db_name.[dbo].DriveShiftDetail where ShiftID = (@RSADriveID)) --ShiftID

if(@Id > 0 )
begin
    set @IsFixedSite = 'Y'
end
end

-- Locate the drive
select
@DriveId = dm.driveid,
@DriveDate = dm.fromdatetime,
@Name=case when dm.drawid>0 then cd.desclong else a.name end

from db_name.[dbo].drivemaster dm
left outer join db_name.[dbo].accounts a on a.accountid=dm.accountid
left outer join db_name.[dbo].centerdetail cd on cd.centerid=dm.centerid
where dm.deleted = 0 and dm.statusid not in (5) and dm.driveid in
    (select driveid from db_name.[dbo].DriveShiftDetail where ShiftID = (@RSADriveID))

if(@DriveId is null or @DriveId <=0 )
begin
--For Historical Drives
select top 1 @DriveId = dm.driveid, @DriveDate = dm.fromdatetime, @Name=case when dm.drawid>0 then cd.desclong else a.name end, @ShiftID = dsd.ShiftID 
from db_name.[dbo].drivemaster dm
join db_name.[dbo].DriveShiftDetail dsd on dsd.DriveID=dm.DriveID
left outer join db_name.[dbo].accounts a on a.accountid=dm.accountid
left outer join db_name.[dbo].centerdetail cd on cd.centerid=dm.centerid

where dm.deleted = 0
and dm.statusid not in (5) 
and dm.externalid like @ExternalIds
and isnumeric(dm.ExternalID)=1

order by dsd.ShiftStart asc, dsd.ShiftID asc

if(@DriveId is null or @DriveId <=0 )
begin
    set @ResultError = 1
    set @ResultMessage = 'Unable to locate drive.' + @PrintInfo
    print @ResultMessage
    return
end
end
set @PrintInfo = '  Trip Number:( ' + convert(varchar(8), @RSADriveID, 1) + ' )  Name: ' + @Name
set @PrintInfo = @PrintInfo + '  Date: ' + convert(varchar(10), @DriveDate, 101)

...

if not exists (select * from db_name.[dbo].DriveShiftActualDetail where ShiftID=@ShiftId)
begin
--Insert Missing DriveShiftActualDetail Rows
INSERT INTO db_name.[dbo].DriveShiftActualDetail
    (ShiftID,FirstTimeDonors,Registered,Voids,QNS,Deferrals,Collected,Contaminated,Cancellations,TurnAways,WalkIns,SelfDeferrals,NoShows,ShiftStart,
    ShiftEnd,HadLunch,LunchStart,LunchEnd,ActualStaff,SignupReduction,UpdateWho,UpdateWhen,UniqueKey,DonorsScheduled,MildReactions,ModReactions,SevReactions)

select
    dsd.ShiftID,0,0,0,0,0,0,0,0,0,0,0,0,dsd.ShiftStart,dsd.ShiftEnd,dsd.HasLunch,dsd.LunchStart,dsd.LunchEnd,
    isnull((select count(distinct personid) from db_name.[dbo].peoplestaffingdetail where shiftid in (select shiftid from db_name.[dbo].staffingeventshiftdetail where driveshiftid=dsd.ShiftID)),0),
    dsd.SignupReduction,dbo.HemasphereUserNo(),getdate(),newid(),dsd.DonorsScheduled,0,0,0

from db_name.[dbo].DriveShiftDetail dsd

where not exists
(
    select dsad.* from db_name.[dbo].DriveShiftActualDetail dsad where dsad.shiftid=dsd.shiftid
)
and dsd.ShiftID=@ShiftId

if(@@Error <> 0)
begin
    select @ErrorMessage = description from master.dbo.sysmessages where error = @@Error
    set @ResultError = 1
    set @ResultMessage = 'ERROR:Error Inserting the Drive Shift Actual record. ' + @PrintInfo
end 
end

Answer 1

每个过程只需要SET NOCOUNT ON;一次，最好是在过程本身的主体顶部。当然，在生成输出的任何语句之前，您都需要它。

因此，例如，我会使用这样的模板来创建过程：

CREATE PROCEDURE dbo.MyProc
AS
BEGIN
    SET NOCOUNT ON;
    ....
END
GO

网上图书这么说是关于SET NOCOUNT ON：

阻止显示受Transact-SQL语句或存储过程影响的行数的消息作为结果集的一部分返回。设置NOCOUNT可以防止为存储过程中的每个语句向客户端发送DONE_IN_PROC消息。对于包含几个不返回大量实际数据的语句的存储过程，或者对于包含Transact-SQL循环的过程，将SET NOCOUNT设置为ON可以提供显著的性能提升，因为网络通信量大大减少。

如我前面所述，在过程主体开始时设置此选项，可以很容易地验证语句实际上在过程中。

请注意，某些软件使用行计数功能，尤其是SQL Server本身用于链接服务器，以确定执行的DML是否成功。设置NOCOUNT ON可能会导致您不期望发生的错误，这可能很难排除故障。还请注意，来自@AaronBertrand的以下评论和建议：

需要记住的一件事(以及我在推荐NOCOUNT时给出的免责声明)是，它可能会干扰某些技术。例如，如果您有旧的ADO代码(在ASP.NET之前)，它会将DONE_IN_PROC消息解释为独立的结果集，因此您现有的代码可能已经有了rs.nextRecordSet()这样的东西来跳过它们。此外，实体框架中的某些模块(可能还有其他的ORM)依赖于这些消息来确定DML操作的成功。所以，如果您使用这些技术，并且已经有了工作代码，那么就不要盲目地将它们添加到您的所有代码中。