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.
- 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)
181 Comments. Leave new
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
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
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?
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?
Fantastic Tips…
Thank you Pinal sir..
very good artical
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.
Nice Information.
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.
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.
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
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.
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
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 :(
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
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
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
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.
Exellent article Pinal…
hi,
while SET NOCOUNT ON , @@Rowcount has been updated. so It may reduce the performance. I dont know exactly. Please suggest me.
Thank you
ROUND(((@Probability1*@Probability2)/((@Probability1*@Probability2)+((1-@Probability1)*(1-@Probability2)))),2)
Nice article and very helpful your articles Pinal Dave…