We will go over how to optimize Stored Procedure with making simple
changes in the code. Please note there are many more other tips, which
we will cover in future articles.
- Include SET NOCOUNT ON statement: With every SELECT
and DML statement, the SQL server returns a message that indicates the
number of affected rows by that statement. This information is mostly
helpful in debugging the code, but it is useless after that. By setting
SET NOCOUNT ON, we can disable the feature of returning this extra
information. For stored procedures that contain several statements or
contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a
significant performance boost because network traffic is greatly
reduced.
Mas Info.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
