How to increase the performance of Stored Procedure

To increase the performance of stored procedure we need follow certain steps

Ref:http://www.SqlAuthority.com

1. Include SET NOCOUNT ON statement

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

2. Use schema name with object name

SELECT * FROM dbo.MyTable — Preferred method
— Instead of
SELECT * FROM MyTable — Avoid this method
–And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc — Preferred method
–Instead of
EXEC MyProc — Avoid this method

3. Do not use the prefix “sp_” in the stored procedure name

CREATEPROCEDURE TPY_Discovery

n Instead of

CREATE PROCEDURESP_Discovery

4. Use the sp_executesql stored procedure instead of the EXECUTE statement

DECLARE

@Query NVARCHAR(100)
SET @Query = N’SELECT * FROM dbo.tblPerson WHERE Age = @Age’
EXECUTE sp_executesql @Query, N’@Age int’, @Age = 25

5. Use TRY-Catch for error handling

BEGIN TRY
–Your t-sql code goes here
END TRY
BEGIN CATCH
–Your error handling code goes here
END CATCH

6. Use Optimize table access with NOLOCK

7. Use Tran and Rollback always while inserting and updating data to table

BEGIN TRY

BEGIN TRAN

INSERT INTO dbo.MyTable VALUES(1,fdf,dfd)

END TRAN

END TRY

BEGIN CATCH

ROLLBACK

END CATCH

8. Use Proper indexes on table

Thank You!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: