Stored Procedure Optimization Tips

by Ravi Tuvar 10:19 PM 0 comments best way to use stored procedure

1: Use TRY-Catch for error handling:

When you write the Stored Procedure , use Try-Catch block statements. That will improve performance of stored procedure:

BEGIN TRY

-- prevent extra result sets from interfering with SELECT statements.

SET NOCOUNT ON;

-- prevent Put SqlStatements

END TRY

BEGIN CATCH

-- handle error condition

END CATCH

2: Try to avoid using temporary tables inside your stored procedure:

Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

3: Use the sp_executesql stored procedure instead of the EXECUTE statement:

using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code.

4: Keep the Transaction as short as possible:

The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction therefore lengthy transaction means locks for longer time and locks for longer time turns into blocking.

5: Use schema name with object name:

The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure's performance.

6: Include SET NO COUNT ON in Sql 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. This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement Setting SET NOCOUNT to ON can provide a significant performance boost.

SET NOCOUNT ON;