Increase your Stored Procedure Performance
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.
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
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
--------------------------------------------------------------
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