Boost Stored Procedure’s Performance

1.       SET NOCOUNT ON/OFF in the stored procedures are help to improve the performance

Eg.

CREATE PROCEDUR dbo.SampleProcedure
AS
SET NOCOUNT ON;
--Write code here
SELECT FirstColumn FROM dbo.SampleTable

SET NOCOUNT OFF;
GO

2.       Schema name with table name and stored procedure name can improve the performance

SELECT * FROM dbo. SampleTable -- Preferred use

EXEC dbo. SampleProcedure -- Preferred use

3.       Don’t use SELECT * in any case. Use the column name 

4.       Don’t use SELECT *  Use IF EXISTS (SELECT 1) for checking

5.       Short Transaction block will help you to improve the transaction

6.       Avoid using SQL Server cursors 

7.       Use the sp_executesql instead of the EXECUTE or exec

Do not use the prefix “sp_”, server will search the system procedure list.