Tips For Writing Good Stored Procedures

1. Fully Qualified Names – Always use the fully qualified name when calling stored procedures. This would be the format database_name.schema_name.table_name.

For example, use EXEC master.dbo.Your_Proc_name instead of EXEC Your_Proc_name This is a very common mistake, which causes an extra trip to the procedure cache to get the execution plan for execution. Also try to use the schema name while creating a procedure. Like: CREATE PROCEDURE dbo.Your_Proc_name instead of CREATE PROCEDURE Your_Proc_name

2. The sp_ prefix – Don’t use the “sp_” prefix in a stored procedure name as the “sp_” prefix is reserved for system stored procedures. Any stored procedure that has the “sp_” prefix will cause an extra lookup in the MASTER database If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will never get executed.

3. Keywords – Use SQL keywords in capital letters to increase readability. Also use proper indentation to increase readability.

4. SET NOCOUNT ON – This returns the message that shows number of rows affected by SQL statement. This can cause extra network traffic and can have some serious impact on performance when the procedure is called frequently.

5. sp_executeSQL and the KEEPFIXED PLAN options – Both sp_executesql and the KEEPFIXED PLAN option avoid the recompilation of a stored procedure. If you want to provide parameterized dynamic SQL, then go for sp_executesql instead of EXEC(proc_name). Here the execution plan for the procedure is stored with the variable name in cache memory. When the variable values are supplied, then the values are simply mapped to the query, hence no need for a recompilation.

6. SELECT vs SET – A single SELECT statement can assign values to different variables and is much faster than multiple SET statements assigning values to multiple different variables.

SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 – 1
instead of

SET @Var1 = @Var1 + 1

SET @Var2 = @Var2 – 1

7.WHERE clauses – In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance.

=, >, <, >=, <=, <>, !=, !>, !<

8.CAST and CONVERT – Try to use CAST instead of CONVERT. CAST is ANSI-92 standard but CONVERT works in MS SQL server only. Also, Convert may be deprecated in future MS SQL releases. It is better to use CONVERT only when you need to format the DATETIME datatype with the style option. CAST cannot do this.

9.Avoid DISTINCT and ORDER BY – If you don’t need the DISTINCT/ORDER BY clause, then try to avoid so. Unnecessary DISTINCT or ORDER BY clauses cause extra work for the database engine. Hence making performance slower.

10.Avoid using cursors – Try to use temporary table/table variables with identity column and then iterate all the tables using WHILE loop and a looping counter, which will map with the identity column.

11.SELECT statements – Try to use only the required number of columns in the SELECT clause instead of using *. Using * returns all columns, which unnecessarily create a fat recordset.

12.Try to use table variables instead of Temporary Tables – Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution.

13.Try to use table variables instead of Temporary Tables – Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution.

If the result set is not containing a huge number of records then you should stick to table variable, otherwise temp table has its advantages. There is a misconception that temp tables always use the tembdb database but table variable do not. Table variables also use tempdb after a certain size.

14.Subquery vs JOINs -In fact most sub queries can be expressed as an equivalent form of JOIN. subquery is faster when we have to retrieve data from large number of tables because it becomes tedious to join more tables. JOIN is faster to retrieve data from database when we have less number of tables. But try to avoid correlated sub queries because it makes the query much slower.

15.Use proper indexes – You can use the help of the data tuning advisor, but it does not gives the proper result all the time. Index scans are much faster than table scans. So identify the table scans from the execution plans. But when a table returns smaller rows, then it is better to use a table scan.


1 Comment

  1. October 9, 2009 at 4:09 pm

    Hey, great blog…but I don’t understand how to add your site in my rss reader. Can you Help me, please 🙂

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: