Edit post Follow this blog Administration + Create my blog

Best Tips to write a Stored Procedure

August 11 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Good Tips to write stored procedure, to improve the performance. Below are the general rules to write good stored procedure.

  • The sp_ prefix :  Don't use the "sp_" prefix in a User created stored procedure name as the "sp_" prefix is reserved for system stored procedures. Any stored procedure that has the "sp_" prefix will be lookup in the MASTER database first, If a stored procedure uses same name in both the user database and a system database, the stored procedure in the System Database only get executed.

  • Use variable as few as possible, if you use more variables it can required more Cache space.

  • SET NOCOUNT OFF This returns the message that shows number of rows affected by executing that SQL statement. This can cause extra traffic and can have some impact on performance when the procedure is called frequently.

  • Provide Fully Qualified Names while calling the Objects like <DbName>.<SchemaName>.<ObjectName>
    instead of direcltly provind the Object Name, because it will 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

  • SELECT statements - Try to use only the required number of columns in the SELECT clause instead of using *, it will return all columns, including un-necessary columns

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

    Declare @a int
    Declare @b int

    Select @a=emp_id,@b=Job_id from emplloyee where email_id = '<search string>'

    instead of

    Set @a = (select emp_id from emplloyee where email_id = '<search string>')
    set @b = (select job_id from emplloyee where email_id = '<search string>')

  • SQL-92 - Always try to use ANSI 92 syntax. Till now the old syntax is working the old syntax will be deprecated in the next release of MS SQL server. As an example, for joining, use

    ex : select * from employee E
           inner join Jobs J
           on E.Job_id = J.Job_id

          instead of
          select * from employee E, Jobd J Where E.Job_id = J.Job_id

  • Try to avoid IN clause. While checking the existence of some values, then use EXISTS instead of IN. Because IN counts the NULL values also, and slower than EXISTS. EXISTS returns only Boolean(Yes/No) value, but IN returns all result set.
  • Avoid DISTINCT/ORDER BY clause : If you don't need the DISTINCT/ORDER BY clause, then try to avoid them. Unnecessary DISTINCT or ORDER BY clauses will cause extra work for the database. so it making performance slower.
  • WHERE clauses - In a WHERE clause, we use different operators to campare and get the result, these also directly affect how fast a query can run. Here are the operators which used in the WHERE clause, ordered by their performance.
        =, >, <, >=, <=, <>, !=, !>, !<
  • Avoid unnecessary conditions in the WHERE Clause.
  • Avoid using cursors - Try to use temporary table/table variables for required result set, then iterate by using while loop.
  • Subquery vs JOINs - more 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.
  • 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.
  • Try to minimize the Dynamic Queries.
  • Last but Not least. Use proper indexes - Indexes is most importent to retrive data as fast as possible. Correct Indexes will give good result. 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.

Share this post
To be informed of the latest articles, subscribe:
Comment on this post
Excellent tips, really useful info...
<br /> Very well explained. Stored procedure are the best way to write the query so that we can use tha same query again and again and we do not have to write it again.<br />
<br /> I agree with most of the above tips as stored procedures are bit complex and need much efforts to make the things clear for the client as well. I think one point to be noted is to make comments<br /> in stored procedures to keep the client in knowledge of the what's going on there.<br />
<br /> You have written concerning lots of points here. There are amazing, and I totally understand what you mean.<br />
<br /> It was a very nice idea! thank you for the information you have shared. Just continue writing this kind of post. I will be your loyal reader. Thanks<br /> again.<br /> <br /> <br /> Reefer Repair<br />