Optimization Tips for Stored Procedure

Ref:
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

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.

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

• 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 in other possible schema before finally deciding to use a cached plan, if available. 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. Therefore, always refer the objects with qualified name in the stored procedure like

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

• Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

• Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

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

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

• Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
• 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. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
• Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

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

Database developers often use stored procedures to increase performance. Here are three tips to help you get the most from your SQL Server stored procedures. We've even thrown in a bonus performance tip for counting records without accessing a table.

Developers often take advantage of stored procedures to enhance the performance of database applications that use Microsoft SQL Server. Stored procedures offer a number of advantages over normal SQL statements: They’re precompiled and preoptimized, and they offer some programmable functionality. The following three tips can help you maximize performance when you’re using stored procedures.

Use NOCOUNT
Microsoft SQL Server offers a set option called NOCOUNT. It's turned off by default so that each operation returns information regarding the number of rows affected. However, applications don’t need this information. If you turn on the NOCOUNT option, stored procedures won’t return row-count information—and therefore, you’ll save the network overhead involved with communicating that information to the client. To set NOCOUNT, simply insert SET NOCOUNT ON as the first statement in the stored procedure, as shown in Listing A.

Running the query to select the author id field returns “(23 row(s) affected)” at the bottom of the query results. On the other hand, running the stored procedure returns only the author id field without the extra message. The reduced network load can be significant if the particular query or update involves a high number of transactions.

Use return values
Queries are often used to verify a piece of information or simply return a single value. When performing code reviews, I have frequently seen an entire row of data returned from a database when only a single integer value was needed. Whenever you need a single value from a query or update, consider using the return value of the stored procedure.

Using a return value is particularly useful when you’re inserting a new record. In terms of code, the only information necessary is the primary key value. To utilize the return values in the stored procedure, simply place the “RETURN <value>” statement at the end of the query as the last command. Listing B shows a modified version of the stored procedure sample that returns a count of the records in the table authors.

Optimize table access with NOLOCK
Most database access does not require transaction safety. This is evident in the popularity of the MySQL database product, which does not supply any record-locking capability (although the 4.0 release is supposed to support transactions). A stored procedure or any access to a database table in SQL can make tremendous performance gains if you use a table hint that lets the SQL engine ignore and not perform locks for a given operation. Take a close look at your applications and you will see many queries that can ignore locking and still return valid information.

Consider Listing C, which shows a stored procedure that loops over the entire set of records in the authors table to obtain a count. Modifying that routine to no longer perform locking yields a tremendous reduction (for 23 records, perhaps a modest reduction) in overhead.

One more tweak for our example code
This last tip, while not particular to stored procedures, improves the performance of our sample code. As shown in Listing D, you can count records without accessing the table. Using the COUNT() function is fine if you need to apply criteria to the table, but, if the application needs to know how many records are in the table, you can obtain this count with a single query. Using this technique can provide a tremendous performance boost when the table contains a large number of records.

The performance tweak in Listing D takes advantage of the fact that if a table has a primary key, it most likely has an index. This technique counts the total rows—it doesn’t count a subset that could be defined by a WHERE clause.

These tips should come in handy the next time a project involves Microsoft SQL Server. With a little work and a few small changes, stored procedures offer a high degree of performance for your most demanding applications

Stored Procedures Optimization Tips

• Use stored procedures instead of heavy-duty queries.
  This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

*****

• Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
  This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

*****

• Call stored procedure using its fully qualified name.
  The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

*****

• Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
  The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

*****

• Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
  The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

*****

• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

*****

• Use sp_executesql stored procedure instead of temporary stored procedures.
  Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

*****

• If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
  The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

*****

• Try to avoid using temporary tables inside your stored procedure.
  Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

*****

• Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
  Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

*****

• Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
  The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

*****

• Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
  To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

*****

· Use TRY-Catch for error handling:

Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

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