SQL Server stored procedure performance

Increase SQL Server stored procedure performance with these tips


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 ” 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.