Completely Free .NET word API

Resources » .NET programming » .NET Framework

Increase your Stored Procedure Performance

Updated: Category: .NET Framework
Author: Member Level: GoldPoints: 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

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.

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


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


Tip :3
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


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.


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


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

Responses to "Increase your Stored Procedure Performance"

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
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India