SQL SERVER – Stored Procedure Optimization Tips – Best Practices

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

SQL SERVER - Stored Procedure Optimization Tips - Best Practices spoptimizationtricks

Solarwinds
  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method
  • Do not use the prefix “sp_” in the stored procedure name: 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:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
    The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

  • Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
  • Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
  • Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Difference Between Update Lock and Exclusive Lock
Next Post
SQLAuthority News – SQL Server Technical Article – The Data Loading Performance Guide

Related Posts

181 Comments. Leave new

  • Hi,

    is there any difference between
    select top 1 * from table where condition

    and

    select 1 from table where condition

    Reply
    • “select Top 1 * ” returns 1 row which satisfies the given condition.
      where as “Select 1” returns Number 1 if there is a record in that table which satisfies the given condition

      Reply
  • Any problem if I use the prefix “sp” in the stored procedure name: If a stored procedure name begins with “SP,” will the SQL server first searches in the master database??? For example : spFact.StoredProcNameA and spRpt.StoredProcNameB

    Reply
  • Hello Pinal,
    I have a question regarding stored procedure.

    I have a stored proc which is about 2000 lines of code. It has several set of logic embedded into it.
    My question is is it better to break this stored procs into different stored procs and run them as part of a stored proc.

    Can i break this proc into 3 different stored procs of 500 lines of code each and run it the same proc. Hope my question is clear

    Reply
  • just an example… I have a stored proc NewUser which lets say takes a new user. In the next steps of the same proc it adds the user to login table,notify’s the admin that there is a new user,and several other steps.

    Can I break this adding user to login table… and notifying the admin.. into different stored procs but run them as different steps inside the NewUser stored proc.

    Is there any advantage doing this?

    Reply
  • Hi, I have this procedure which is used to display a report. its taking an acronym parameter. the procedure is as below:

    CREATE PROCEDURE [dbo].[RISCAppRISCRatingHistory]
    (
    @Acronym varchar(500)
    )
    as
    begin
    SET ARITHABORT ON

    select DISTINCT Acronym,Rating as RISCRating,
    ProductionAccess as [Production access restrictions],
    NonProductionAccess as [Non-production access restrictions],
    Status,
    CUID as [User ID],[TimeStamp], CONVERT(datetime, [timestamp]) as [Timestamp_Sort]
    From dbo.RISC_Long_Rating_History
    where Acronym=@Acronym AND Status IN(‘Accepted’,’Rejected’) and Rating”
    ORDER BY [Timestamp_Sort] desc

    end

    now i want to add an condtion to check for a particular rating in d table and display only particular columns if the acronym has tht rating, else display al the columns of the table.

    so i modified the above procedure like this:

    ALTER PROCEDURE [dbo].[RISCAppRISCRatingHistory]
    (
    @Acronym varchar(500)
    )
    as
    begin
    SET ARITHABORT ON

    if(Rating IN (‘Critical Infrastructure Information Theft’,’Network Sabotage’))
    BEGIN
    select DISTINCT Acronym,Rating as RISCRating,
    ProductionAccess as [Production access restrictions],
    NonProductionAccess as [Non-production access restrictions],
    Status,
    CUID as [User ID],[TimeStamp], CONVERT(datetime, [timestamp]) as [Timestamp_Sort]
    From dbo.RISC_Long_Rating_History
    where Acronym=@Acronym AND Status IN(‘Accepted’,’Rejected’) and Rating”
    ORDER BY [Timestamp_Sort] desc
    END

    else
    BEGIN
    select DISTINCT Acronym,Rating as RISCRating,
    ProductionAccess as [Production access restrictions],
    NonProductionAccess as [Non-production access restrictions],
    Status,
    CUID as [User ID],[TimeStamp], CONVERT(datetime, [timestamp]) as [Timestamp_Sort]
    From dbo.RISC_Long_Rating_History
    where Acronym=@Acronym AND Status IN(‘Accepted’,’Rejected’) and Rating”
    ORDER BY [Timestamp_Sort] desc
    END
    end

    but im getting an error tht rating is not a valid column name in the if condition.

    Can anyone help?

    Reply
  • Prathamesh Purandare
    December 8, 2012 5:01 pm

    Fantastic Tips…
    Thank you Pinal sir..

    Reply
  • very good artical

    Reply
  • Vamsee Krishna A (Software Engineer)
    December 20, 2012 3:16 pm

    Hi Pinal Dave sir, Its very pleasure in reading topics in your blog. I always got doubts how to check which query executes fast. I am biggest fan of the stored procedure. But when it comes to write complex logic, I usually have more than one way to get the result. So how can i check the performance. Could you suggest easy way. I don’t want use extra code for checking performance.

    Reply
  • Nice Information.

    Reply
  • Sandesh Narkar
    January 22, 2013 7:43 am

    Hi,

    Pinal can we use select * statement in stored procedure?
    and if we want all the field from the table by having only one inout parameter, then at the time of execution it is require to declear all the output attributes
    or to declear only the input value inthe case if we use the select * query.
    Please reply .
    I am new for stored procedure.

    Reply
    • Hi Sandesh, since I don’t see any reply to your question I’ll answer you.

      You dont have to declare all the output attributes because you are selecting from a table, so the return attributes come from the result of the select.

      But it’s not a good practice to use “SELECT *”, for performance reasons and unexpected behaviors in case you table change.

      Reply
  • In SQL Server 2008 R2 the select count (1) and count (*) have the same execution plan. I think I’ll use count (*) because it is a standard

    Reply
  • Hi
    I’m currently working on the data migration project.

    I’m using cursor for looping so that I can massage the data record by record and also migrate the child data together.
    From your article, you mention try to avoid using cursors, should replace with SET-based approach.
    Can you please tell you should I use SET-based in below sample

    Sample

    Account_tbl
    Account_ID
    Account_Name
    Tax_Exempt_IND
    Duty_Exempt_IND
    Status
    Status_Change_Date
    Reference_Text

    Account_Tel_Number_tbl
    Tel_Number_ID
    Account_ID
    Tel_Number
    Status

    My stored procedure

    SET NOCOUNT ON
    SELECT Account_ID, Account_Name, Tax_Exempt_IND , Duty_Exempt_IND , Status, Status_Change_Date, Reference_Text
    INTO #Account_Temp
    FROM Account_tbl
    WHERE ((Status IN (‘A’, ‘F’, ‘U’) OR
    (Status IN (‘C’, ‘E’, ‘R’) AND DATEDIFF(MONTH, Status_Change_Date, GETDATE()) <= 6))

    UPDATE #Account_Temp
    SET Tax_Exempt_IND = ‘N’,
    Duty_Exempt_IND = ‘N’,
    Reference_Text = ‘’

    DECLARE Account_Cursor FOR
    SELECT Account_ID, Account_Name, Tax_Exempt_IND , Duty_Exempt_IND , Status, Status_Change_Date, Reference_Text
    FROM #Account_Temp

    OPEN Account_Cursor

    FETCH NEXT FROM Account_Cursor
    INTO @Account_ID, @Account_Name, @Tax_Exempt_IND, @Duty_Exempt_IND , @Status, @Status_Change_Date, @Reference_Text

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC [dbo].[Transform_Account_Tel_Number] @Account_ID

    INSERT INTO [Converted_Database].[dbo].[Account_tbl] (Account_ID, Account_Name, Tax_Exempt_IND , Duty_Exempt_IND , Status, Status_Change_Date, Reference_Text)
    VALUES (@Account_ID, @Account_Name, @Tax_Exempt_IND, @Duty_Exempt_IND , @Status, @Status_Change_Date, @Reference_Text)

    FETCH NEXT FROM Account_Cursor
    INTO @Account_ID, @Account_Name, @Tax_Exempt_IND, @Duty_Exempt_IND , @Status, @Status_Change_Date, @Reference_Text

    END

    CLOSE Account_Cursor;
    DEALLOCATE Account_Cursor;

    DROP #Account_Temp

    SET NOCOUNT OFF

    This [dbo].[Transform_Account] will loop the data in Account_tbl table and called [dbo].[Transform_Account_Tel_Number] stored procedure.
    In [dbo].[Transform_Account_Tel_Number], it will do another loop to migrate the records in [dbo].[Account_Tel_Number_tbl] records by records.

    Can you please tell me how to use SET-based approach for this??

    My current problem is, I have huge database to loop.
    I have more than 2mils account in [dbo].[Account_tbl], each Account has about 6 child table to loop. Each of them also has more than 2mils to loop. It takes more than 10 hours to finish.
    I’m look at how to make it fast.

    Thanks for help.

    Reply
  • Hi,

    I have a procedure which runs successfully 95% of the time. But times out some times. Getting the error ‘System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding’. I have set sql connection timeout to 2 mins and max pool size as 250 in web.config.

    Below is the procedure:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROC [dbo].[spFunChat_SearchUsers]
    (
    @UserID int,
    @Gender char(1),
    @MinAge int,
    @MaxAge int,
    @CurUserGender char(1),
    @CurUserAge int,
    @CurUserLocation varchar(100)
    )
    AS
    BEGIN
    DECLARE @ModifiedMinAge int
    DECLARE @ModifiedMaxAge int
    DECLARE @IsAgeWeightage bit

    SET NOCOUNT ON;
    If (@MinAge = 16 and @MaxAge =99)
    BEGIN
    Select @IsAgeWeightage = 1, @ModifiedMinAge = @CurUserAge – 2, @ModifiedMaxAge = @CurUserAge + 5
    END

    SELECT ChatUserID, Age,Location,Gender,UserID,Status,GenderWeightage,AgeWeightage,LocationWeightage,ID, GenderWeightage + AgeWeightage + LocationWeightage as TotalWeightage
    FROM
    (
    SELECT
    fcu.ChatUserID,fcu.Age,fcu.Location,fcu.Gender,fcu.UserID,fcfl.Status, fcu.ID,
    GenderWeightage =
    CASE
    WHEN @Gender = ‘A’ and @CurUserGender = ‘M’ and fcu.Gender = ‘F’ THEN 50
    WHEN @Gender = ‘A’ and @CurUserGender = ‘F’ and fcu.Gender = ‘M’ THEN 50
    ELSE 0
    END,
    AgeWeightage =
    CASE
    WHEN @IsAgeWeightage = 1 and fcu.Age between @ModifiedMinAge and @ModifiedMaxAge THEN 25
    ELSE 0
    END,
    LocationWeightage =
    CASE
    WHEN @IsAgeWeightage = 1 and @Gender = ‘A’ and fcu.Location = @CurUserLocation THEN 25
    ELSE 0
    END
    From dbo.FunChatUsers fcu
    LEFT OUTER JOIN dbo.FunChatFriendsList fcfl
    On fcu.UserID = fcfl.FriendID and fcfl.UserID = @UserID
    Where fcu.IsLoggedIn = 1
    and fcu.UserID@UserID
    and (fcu.age>=@MinAge and fcu.Age<=@MaxAge) and (@gender = fcu.gender OR @Gender='A')
    and fcu.UserID NOT IN (Select FriendID from dbo.FunChatFriendsList where UserID = @UserID and Status = 'Blocked')
    ) Search
    order by TotalWeightage desc, ID

    END

    Any help would be much appreciated.

    Thanks

    Reply
  • very informative…I am trying to optimize a query written by my team member that is taking 28 hours to run and I am not sure whether I should change the logic or just use the optimizatin techniques :(

    Reply
  • Hi, how can we avoid cursor from the following procedure?

    CREATE PROCEDURE [dbo].[TestLeave] AS
    SET NOCOUNT ON
    DECLARE @MemberID INT, @TypeID INT, @StartDate DATETIME, @ENDDATE DATETIME
    DECLARE CUR_EMPLOYEELEAVE CURSOR FOR
    SELECT MemberId, TypeID, StartDate, EndDate
    FROM Leave
    OPEN CUR_EMPLOYEELEAVE
    FETCH NEXT FROM CUR_EMPLOYEELEAVE
    INTO @MemberId, @TypeID, @StartDate, @EndDate
    WHILE @@FETCH_STATUS = 0
    BEGIN

    WHILE CONVERT(VARCHAR, @StartDate, 101) CONVERT(VARCHAR, @EndDate, 101)
    BEGIN

    INSERT INTO #TempLeave (MemberId, TypeID, StartDate,EndDate )
    VALUES (@MemberId, @TypeID, @StartDate, DATEADD(hour, 10, @StartDate))
    SET @StartDate = DATEADD(day, 1, @StartDate)
    END
    IF CONVERT(VARCHAR, @StartDate, 101) = CONVERT(VARCHAR, @EndDate, 101)

    BEGIN
    INSERT INTO #TempLeave (MemberId, TypeID, StartDate, EndDate )
    VALUES (@MemberId, @TypeID, @StartDate, @EndDate)

    END
    FETCH NEXT FROM CUR_EMPLOYEELEAVE
    INTO @MemberId, @TypeID, @StartDate, @EndDate
    END
    CLOSE CUR_EMPLOYEELEAVE
    DEALLOCATE CUR_EMPLOYEELEAVE
    SET NOCOUNT OFF

    Reply
  • Atul kumar Yadav
    June 18, 2013 2:41 pm

    Hello Brother,

    Store procedure taking 5 minutes to execute but if query of which i written procedure then it will take on 4 sec. I did google and find parameters sniffing for improving procedures performance so implemented that and now it still taking 3 minutes.
    What should i do?

    Thanks
    Atul yadav

    Reply
  • Manish Solanki
    August 31, 2013 4:42 pm

    Hello Pinal Sir,
    I have 2 Simple Query that i wants to optimize and made into Single.
    I want to reduce the time( 2 Time Selection + 2 time Index Scanning ) that taken by both Update Query on Single Table.

    I wants Query 1 + Query 2 = SQLAuthority_Query.

    Always my final and Last hope is always you and your blog…..

    Query 1 : Deduct Amount from Source Customer Account
    =================================================
    Update dbo.T_BANK
    SET Balance= Balance-@TAmount
    WHERE Bank_Customer_ID=@Source_Customer_ID

    Query 2 : Amount Added to the Destination Customer Account
    =================================================
    Update dbo.T_BANK
    SET Balance= Balance+@TAmount
    WHERE Bank_Customer_ID=@Dest_Customer_ID

    Reply
  • Very helpful article, especially about not starting a stored proc’s name with ‘sp_’. Working at this for 12 years I hadn’t realized that.
    By the way, as other’s noted, I found a tremendous performance boost after adding temp table indexing even though the proc used the table only once, due to it being joined to other tables.
    Didn’t always prove worth it, I supposed if the join wasn’t a big one, but here and there it made a big difference.

    Reply
  • Exellent article Pinal…

    Reply
  • hi,
    while SET NOCOUNT ON , @@Rowcount has been updated. so It may reduce the performance. I dont know exactly. Please suggest me.

    Thank you

    Reply
    • ROUND(((@Probability1*@Probability2)/((@Probability1*@Probability2)+((1-@Probability1)*(1-@Probability2)))),2)

      Reply

Leave a Reply

Menu