TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Performance Tuning of Stored Procedure
Rahul Chavan
Apr 08, 2016
27
k
0
0
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
In this blog you will learn about Performance tuning of Stored Procedure.
Include SET NOCOUNT ON
• By default SQL returns the number of rows affected by DML statements. By default SET NOCOUNT is ON.
• It is mostly required for debugging purposes or processing based on the return values. If you do not require this information sent to user explicitly SET NOCOUNT OFF.
Benefit:
It will reduce Network traffic.
SQL Code:
--Below code will not return Number of rows affected by DML statements,
SET
NOCOUNT
OFF
;
Refer to database objects by a schema name and the object name instead of only object name,
• If you do not specify the schema/ fully qualified name of the object, SQL Server will search the object in all possible schemas and decide which schema to use for object.
• Instead if you use fully qualified object name, you can save search and decide time.
Benefit:
Reduce search and decide time.
SQL Code:
--Existing / Old Code
SELECT
*
FROM
Customer
--Best Practice /New Code
SELECT
*
FROM
dbo.Customer
Reference:
SQL Server Best Practices – Implementation of Database Object Schemas
Avoid using sp_ prefix in Stored Procedure Name
sp_ prefix designates system stored procedures. When you use sp_ prefix SQL server search Master database first and if it not able to find the find the Stored procedures then it search current session database.
Benefit:
Reduce search time in master database.
Reference:
SR0016: Avoid using sp_ as a prefix for stored procedures
Avoid Lengthy Transaction:
• Long transaction block will decrease performance by blocking resource.
• So keep transaction as short as possible.
With IF Exist statement Avoid using Select *, instead use Select (1)
Select * statement will process all the records in table. Select (1) will check for 1 record in table or condition satisfying the condition.
SQL Code:
--Existing / Old Code
IF EXISTS (
SELECT
*
FROM
sys.objects
WHERE
object_id = OBJECT_ID(N
'ProcName'
)
AND
type
IN
( N
'P'
, N
'PC'
) )
--Best Practice /New Code
IF EXISTS (
SELECT
1
FROM
sys.objects
WHERE
object_id = OBJECT_ID(N
'ProcName'
)
AND
type
IN
( N
'P'
, N
'PC'
) )
Avoid using Select *, instead use Select ColumnName
When you require all columns from table, then only use Select *. For all other cases use Select ColumnNames.
Benefit: Avoid Network traffic
SQL Code:
--Existing / Old Code
SELECT
*
FROM
dbo.Customer
--Best Practice /New Code
SELECT
CustomerId, CustomerName, CustomerAddress
FROM
dbo.Customer
Use NOLOCK statement in all cases or it should not be used for all cases.
If stored procedure uses parameter based code block (if/else) and if you use WITH (NOLOCK) in some cases only then it will lead to bad execution.
It should be used in all cases or it should not be used at all.
SQL Code:
Create
Procedure
GetCustomerInfoByLob
(
@LOBCd NVARCHAR(10)
)
As
BEGIN
--Select All PL Customers
IF @LOBCd=
"PL"
SELECT
*
FROM
PLData
WITH
(NOLOCK)
--Select All CL Customers
ELSIF @LOBCd=
"PL"
--Below Line of Code will lead to Bad Execution code.
SELECT
*
FROM
PLData
--Select All(Both PL and CL) Customers
ELSE
SELECT
*
FROM
PLData
WITH
(NOLOCK)
UNION
SELECT
*
FROM
PLData
WITH
(NOLOCK)
END
Performance tuning of Stored Procedure
Next Recommended Reading
Tips To Increase SQL Server Stored Procedure Performance