How to write a optimistic stored procedure ?

In this article we will see how to write a stored procedure with optimization by making simple changes in the code. Following are the set of statements are helpful to improve the performance of a stored procedure.

SET NCOUNT ON Statement

• Microsoft SQL Server provides a set option called NOCOUNT. It is turned off by default so that each operation returns information regarding number of rows affected. But its useless once we done with result set. And our applications do not this information.
• If you set NCOUNT in ON mode, stored procedure won't return affected rows information, so that we can avoid or reduce the network traffic and load. And It clearly happens when the truncations occur on large amount of records.

• Example: Using NOCOUNT option

  CREATE PROCEDURE dbo.procSample  AS    SET NOCOUNT ON    SELECT   au_id    FROM      author

Access the table with NOLOCK

•  In any stored procedure, if we see the statements without having NOLOCK on table will definitely gain more performance. So that, we can use NOLOCK option when you perform a transaction on tables.

• Example: NOLOCK sample

  SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 2

Use RETURN values

• Whenever you need a single value from a query or update, consider using the return value of the stored procedure.

• Example: Sample that returns a count of the records in the table authors

  CREATE PROCEDURE dbo.procSample AS    SET NOCOUNT ON    DECLARE @recCount int    SELECT   @recCount = COUNT(au_id)    FROM     authors    RETURN @recCount

Tuning the query

• Prepare the query effectively without having sub selects and functions with in the functions

Provide Indexes

• Make sure that the tables are having Indexes when we are going to perform large number of transactions on a single columns

Do Not Use the Sp_ Prefix for Custom Stored Procedures

• If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

Use IF EXISTS (SELECT 1) instead of (SELECT *):

• To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below: 
•  

  Example: Check If Record Exists

• IF EXISTS (SELECT 1 FROM Production.Product
            WHERE ProductNumber = 'AR-5381'
            )
           
    PRINT 'AR'
ELSE
    PRINT 'A'