Chapter 2: Optimizing and Debugging

In this chapter, we will be discussing optimizing SQL Server's stored procedures. Here, our focus will not be on optimizing the SQL statements themselves , but instead, how SQL statements can be optimized within the context of a stored procedure.

In this chapter, we will also discuss error handling, signaling error conditions, and debugging stored procedures.

Optimizing Stored Procedures

The performance of a stored procedure is largely dependent on the nature of the T-SQL code within it; however, there are a few things you can do to improve the overall performance of the stored procedure. As we mentioned, in this section, we won't specifically look at optimizing T-SQL code; instead, we will look at the general optimization techniques that we can apply to any stored procedure.

Let's now take a look at these general optimization techniques.

SET NOCOUNT ON

SET NOCOUNT ON is probably the most important optimization setting for stored procedures. By using it, we can greatly reduce the time and network bandwidth used for executing stored procedures, especially the complex ones.

When a T-SQL DML statement ( SELECT , INSERT , UPDATE , or DELETE ) is executed, statistics relating to the number of rows affected by those statements are passed back to the client for interpretation. For example, suppose we execute the following statement in Query Analyzer:

SELECT * FROM dbo.Customers WHERE CustomerID LIKE 'AN%'

After execution, we will receive a rowset, and the statistic about the number of rows affected, such as:

(2 row(s) affected)

This is not particularly bothersome, and at times, can be even useful when executing a single command in an adhoc fashion. Now, consider the following example of a stored procedure:

CREATE PROCEDURE TestDataGenerator AS DECLARE @count INT IF OBJECT_ID('dbo.TestDataTable') IS NULL BEGIN CREATE TABLE dbo.TestDataTable ( IDCOL INT PRIMARY KEY, VarCharCol VARCHAR(255) ) END SELECT @count=1 WHILE @count<100 BEGIN INSERT TestDataTable(IDCOL, VarCharCol) SELECT @count, REPLICATE(CHAR((@count%26)+65),@count%255) SELECT @count=@count+1 END

This procedure creates a test table (if it doesn't already exist) and adds 100 rows to the test table; perhaps, not the most practical of stored procedures, but it demonstrates the point. Now execute this stored procedure by using the following command:

EXEC

TestDataGenerator

We don't receive any rowsets back, as this is not the purpose of this particular procedure. However, we will receive 100 messages, 1 message for every INSERT operation, telling us that a row has been inserted:

(1 row(s) affected) (1 row(s) affected) (1 row(s) affected) ...

Each one of these messages consumes network bandwidth, which is simply a waste. Imagine the impact if we were inserting, for example, 1000 rows. A far more efficient approach would be to use the SET NOCOUNT command to disable the sending of these messages within the scope of the stored procedure. For example:

ALTER PROCEDURE TestDataGenerator AS SET NOCOUNT ON DECLARE @count INT ...

Now, if we execute the stored procedure, the 100 rows are still inserted, as before; however, no unnecessary messages are sent to our client application for each INSERT , thereby reducing network usage.

CREATE PROCEDURE

procedure_name

WITH RECOMPILE ...

EXEC procedure_name parameters WITH RECOMPILE