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:
WHERE UserName = @UserName
Try this:
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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s