Resources » .NET programming » .NET Framework

Increase your Stored Procedure Performance


Posted Date: 15-Mar-2012  Last Updated:   Category: .NET Framework    
Author: Member Level: Diamond    Points: 15


In this article I have explained how to increase SQL server stored procedure perormance. This article will tell you how to create and execute stored procedure and different tips to increase performance of sql server stored procedures.



Increase your Stored Procedure Performance


introduction
Stored Procedure is prelogic SQL group of statements compiled in to single execution road. Very famous in SQL Server.
can be used for reducing codebehind load. Stored procedures are very fast when compared to ordinary sql statements where
the sql statements will follow some steps to fetch data from database

SQL stored procedure are always good for performance, as it reduce network traffic, because your end user will send only stored procedure name to server
instead of large heavy-duty queries in text format.
Stored procedure are good in terms of security, we can give various access permissions to users to execute the stored procedure to work with
limited set of the columns and rows

Start with Syntax
I know, all of us are well-known to stored procedures concept but here is syntax for fresher's.
SP_syntax


To execute stored procedure we have to use exec command


exec procName


Improving Performance
Now we come to main point, "Improving performance".
see the below tips for sql stored procedures

Tip :1
Include SET NOCOUNT ON statement :
Microsoft SQLServer offers No COUNT statement, it's bydefault "Turned OFF" so when we select any data from database, SQL returns us no of rows affected.
that in turn slow down our process. however application doesn't need this information. If we "Turned ON" No count, it will not return any rows count from database.
and therefore, we will save the network overhead involved with communicating that information. Simply insert SET NOCOUNT ON as the first statement in the stored procedure to
set NOCOUNT Turned ON
NOCOUNT



--------------------------------------------------------------

Tip :2
Access Table with NOLOCK :
Most of the time our database does not require transaction safety access for that we have to make access database with NOLOCK statement.
that will improve performance. basically SQL server engine parse the NOLOCK statement and not perform locks for a given operation.
here is example
NOLOCK



--------------------------------------------------------------

Tip :3
Use RETURN :
Many time we have seen an entire row of data returned from a database when we want only a single integer value. from database. This is additional overhead to database so use "return"
statement in stored procedure. same above example can be used here
RETURN


--------------------------------------------------------------

Tip :4
Use schema name stored procedure name:
we should use schema name before stored procedure name as it helped in directly finding the stored procedure in given schema,
otherwise SQL will search for schema name in all other possible schema before finally deciding to use a cached plan.
This searching and deciding process will leads to COMPILE lock on stored procedure and relatively decreases the stored procedure's performance, check following example


//here is full procedure name with schema
Exec localhost.database1.test1_stored_procedure


--------------------------------------------------------------

Tip :5
Do Not Use the "SP_" as Prefix for Stored Procedure names:
Do not use "sp_" prefix for custom stored procedure cause SQL Server always looks in the master database for stored procedure
that begins with the sp_ prefix. Though you are given a schema then only SQL server will check master database list first

--------------------------------------------------------------

Tip :6
Do not try to use DDL statements inside a stored procedure that will reduces the chance to reuse the execution plan.
DDL statements like CREATE,ALTER,DROP,TRUNCATE etc.

--------------------------------------------------------------

Thanks
Someone said "There is no Improve Performance checkbox in any software project" this is the fact, Performance enhancement is never ending process,
There are many tips that will help you to make your SQL run faster, we will cover them one by one.
Suggestion are most welcome

Thanks
koolprasad2003


Did you like this resource? Share it with your friends and show your love!

Responses to "Increase your Stored Procedure Performance"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India