Two easy stored procedure tips for better performance

Here are two quick, easy changes you can make to your T-SQL stored procedures that will improve performance.
 
First, always make sure to owner-qualify your table names. For example, instead of:
 
SELECT UserID
FROM User
WHERE UserName = @UserName
 
Try this:
 
SELECT UserID
FROM dbo.User
WHERE UserName = @UserName
 
Owner qualification avoids implicit object resolution, and can help avoid cache misses for your stored procedures.
 
The second tip is to use SET NOCOUNT ON in your T-SQL stored procedures. This prevents SQL Server from returning the DONE_IN_PROC message (for example: "8 rows affected") back to the caller, which saves some CPU on SQL Server and reduces network traffic. Turning this on does not prevent @@ROWCOUNT from working, but it can cause problems if the caller expects the rowcount to be returned.
 

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s