How to Optimize Stored Procedure Optimization?

06 Aug

There are many tips and tricks for the same. Here are few:

  • Include SET NOCOUNT ON statement.
  • Use schema name with object name.
  • Do not use the prefix “sp_” in the stored procedure name.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *).
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
  • Try to avoid using SQL Server cursors whenever possible.
  • Keep the Transaction as short as possible.
  • Use TRY-Catch for error handling.
Leave a comment

Posted by on August 6, 2013 in MS SQL Server


Tags: , , , , , ,

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 )

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

%d bloggers like this: