Tips To Write A Good Stored Procedure

Some Tips to write a good stored procedure

Coding SPs is a very common task in the database world. This is done not only by database developers, but also by application developers. Most of the smart DBA also keep himself/herself involved in writing and reviewing stored procedures. One of the interviews I was asked “How good you are in writing complex stored procedures” and “What are the major tips you keep in your mind when you write the stored procedures as DBA”.  Notice the last word added by interviewer “as DBA”. You should consider all the performance issues when you answer this question. 

Stored procedures are having more importance in the database as it reduces network traffic and improve performance. In this post I have just tried to add some tips to write good stored procedures. I hope this will be helpful for fresher and for the experienced developers (Application/database).

1. Always maintain better readability in the stored procedure. For this use proper indentation and make sure you specify SQL keywords in upper case. For Example instead of ‘Select’ write ‘SELECT’. This makes other developers to understand the code properly. If you do this at first site you are the good programmer/developer.  

2. Until you need so many variables in the SP, use as few as possible variables. Remember doing this will free some spaces in cache and performance would be better.

3. We should avoid dynamic queries in the stored procedures. In case if dynamic queries each time SQL server recompiles the statement. It has very bad performance impact.

4. When you are calling stored procedures, always use fully qualified names. The format for fully qualified name could be “DB_NAME.SCHEMA_NAME.TABLE_NAME”.  Whenever you create the SP always use schema name in the create procedure statement even though the schema is dbo.

5. As I mentioned before SPs reduces network traffic and increase the performance. I have seen so many SPs written by developers where SET NOCOUT ON has not been used. If you don’t use this SQL server returns the message that shows number of rows affected. This can cause extra network traffic and can have major impact on the performace.

6. DO NOT USE ‘sp_’ prefix in a stored procedure name. This is reserved for system stored procedures in the master database. If you use the prefix ‘sp_’ then basically you are making SQL server to have an extra look up in the master database.

7. Most of the time we call other SP(s) in one SP. We should always use sp_executeSQL instead of directly EXEC.

8. When we assign value to a variable, we use SET. We can also use SELECT statement. The difference between both is: By using SET you can assign value to one variable at a time but by using SELECT you can assign values to multiple different variables. So, instead of multiple SET statement use SELECT statement. This is much faster than multiple SET statements.

9. Avoid unnecessary conditions in the WHERE Clause. Avoid using IN instead use EXISTS. This gives better performance as IN considers the NULL value also. Remember result set for IN is heavier than EXISTS.

10. Avoid using DISTINCT and ORDER BY until you need this. It makes the performance slower.

11. We should not use CURSORS instead use temp table/table variable. If you need iteration then create the table with identity column and use WHILE loop.

12. Avoid using “SELECT *”. Instead specify column names (required) in the SELECT clause.

13. What we should use SUBQUERY or JOIN? Remember SUBQUERY works faster in large tables whereas JOINS works faster in small tables. You should use both keeping this in mind. Also avoid using correlated sub queries and this affect the performance of the query.

14. 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. 

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. 

Remember for the good performance rules could be broken if needed. We should keep in mind that for any firm database performance is the ultimate goal.

Hope these tips help you to some extent. There could be some other tips that I can add later.