Here are few tips to tune up stored procedures in SQL Server. I gathered these after doing some search on Google:

• Include the ‘SET NOCOUNT ON‘ statement to stop the message indicating the number of rows affected (Reduces network traffic)
• Break down the stored procedure in parts and call them from 1, esp. if you know that few of them won’t require re-compilation
• Add ‘WITH RECOMPILE‘ to CREATE PROCEDURE statement if query varies each time it is run (Prevents reusing the execution plan. SQL Server does not cache a plan for this procedure)
• All objects that are called within the same stored procedure should be referred to in the format of object_owner.object_name (A small performance boost, since SQL Server performs name resolution on the objects)
• Use the new ANSI JOIN syntax instead of the old style joins (The new join syntax has a slight performance advantage over the old way of using the WHERE clause for a join)
• Minimize the number of table lookups especially if there are sub-query SELECTs or multicolumn UPDATEs

Rate this topic:

(No Ratings Yet)

 Loading ...