Geeks With Blogs
Catherine Russell Architect, President of "Women In Information Technology" Group

5 Stored Procedures Optimization Tips
1. Use stored procedures instead of heavy-duty queries. This can reduce network traffic as your client will send to the server only the stored procedure name (perhaps with some parameters) instead of all the text from a large heavy-duty query. Stored procedures can be used to enhance security as well. For example, you can give the users permission to execute the stored procedure to work  with restricted sets of columns and data.

2. Call stored procedures using their fully qualified name. The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance.

3. Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database. The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend using the prefix "sp_" in user-created stored procedure names as SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database. When you have the stored procedure with the prefix "sp_" in a database other than master, the master database is always checked first. If the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

4. Use the sp_executesql stored procedure instead of the EXECUTE statement. The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improves readability of your code when many parameters are used.

5. If you have a very large stored procedure, try to break down the stored procedure into several sub-procedures, and call them from a controlling stored procedure. The stored procedure will be recompiled when any structural changes are made to a table or view referenced by the stored procedure (an ALTER TABLE statement, for example), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, there's a chance that only a single sub-procedure will be recompiled, while other sub-procedures will not.

 

Posted on Friday, June 25, 2010 1:21 PM ASP.NET | Back to top


Comments on this post: 5 Stored Procedures Optimization Tips

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © CatherineRussell | Powered by: GeeksWithBlogs.net