Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 216

Another benefit of using stored procedures is minimizing the length of the command text that an
application sends over the network. Imagine the overhead of sending a group of T-SQL statements that
are several hundred or even thousands of lines long over a network. Now imagine calling a stored
procedure, which would only require one line of code to be sent over a network, and still perform the
same task.

Some helpful information concerning improving T-SQL stored procedure performance is given below.

NOCOUNT
When I first started working with SQL Server many stored procedures I saw would have the state-
ment, SET NOCOUNT ON, as the first line. A colleague explained that this was to help improve the
performance of the stored procedure. I didn't understand how this would help. After all, the only effect I
knew of was that this would cause the number of rows affected by a T-SQL statement to be suppressed.
This hardly seems to be a performance concern. However, I later learned that this statement has another
effect. It also causes the suppression of sending of DONE_IN_PROC messages to the client. If a stored
procedure has multiple statements, and NOCOUNT is off (which is the default) it will send this mes-
sage every time one of the statements completes. You can imagine that if a stored procedure executes
statements hundreds or even thousands of times perhaps via loop or cursor structures, these messages
could become a significant source of network traffic. Additionally, most applications don't need these
messages, so why send them. Therefore, disabling them can greatly improve performance by reducing
network traffic. I even heard of an application that had an order-of-magnitude performance improve-
ment by setting NOCOUNT ON. So, always include SET NOCOUNT ON as the first line in every stored
procedure you write. Note that setting NOCOUNT ON does not effect the use or population of the system
variable @@ROWCOUNT.

Avoiding Unnecessary Database Access
This sounds obvious, but I've seen plenty of examples where this isn't done. Since stored procedures
are similar to code modules in a programming language, you can use some of that functionality to
minimize impact to the database. Use variables, loop structures, conditional checks, and control-of-flow
when possible. The following example illustrates this.

Perhaps the most common problem concerns stored procedures that save data to a table. For example,
suppose you need to create a stored procedure to save data to the Billing table listed earlier in the section
''Default Values.'' A typical procedure would be:

CREATE PROCEDURE saveBilling (@BillTS AS SMALLDATETIME,

@Amount AS SMALLMONEY,
@BillingID AS INT NULL) AS

SET NOCOUNT ON
IF (SELECT COUNT(*) FROM Billing WHERE BillingID = @BillingID) = 0
BEGIN

INSERT INTO Billing(BillTS, BillAmount) VALUES (@BillTS, @Amount)

END
ELSE
BEGIN

UPDATE Billing
SET BillTS = @BillTS,
BillAmount = @Amount
WHERE BillingID = @BillingID

END