I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON
.
I've read a couple of articles on the subject:
SET NOCOUNt ON Improves SQL Server SP Performance
But what I don't really understand is if this is needed once per procedure, or would it need to be inserted every time you have "begin / end"
For example, in the procedure below – should I insert the SET NOCOUNT ON
right have the last variable is set: set @PrintInfo = 'No Trip Number...
Or would I need to insert the SET NOCOUNT ON
after every "begin" in the procedure:
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
Best Answer
You only need to
SET NOCOUNT ON;
once per procedure, preferably at the top of the body of the procedure itself. Certainly you'd need it prior to any statement that generates output.So, for instance, I'd use something like this as a template for creating procedures:
Books Online says this about
SET NOCOUNT ON
:Setting this option at the start of the procedure body, as I've outlined above, makes it easy to verify the statement is actually in the procedure.
Be aware, the row count feature is used by certain software, not least of which is SQL Server itself for Linked Servers, to ascertain whether or not the executed DML was successful. Setting
NOCOUNT ON
may cause errors to occur that you are not expecting, and that can be difficult to troubleshoot. Also note, the following comment and advice from @AaronBertrand: