With help of following tips we can increase the
performance of stored procedure in Sql Server 2005 which explained as follow:
1: Use TRY-Catch for error
handling:
When you writing Stored Procedure user 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;