Talk to Webmaster
Tony John

Facebook
Google+
Twitter
LinkedIn

Online Membersnaveensanagasetti
Ultimaterengan
Jeeva
Deepashri
Ranipriya
Sidharthan
More...

Join our online Google+ community for Bloggers, Content Writers and Webmasters

Resources » Interview Tips » Technical Interviews

Hoe to Increase SQL Server stored procedure performance

Posted Date: 11-Jul-2012	Category: Technical Interviews
Author: chiranjita nayak	Member Level: Silver	Points: 10

Every Body knows that store procedure is better than Sql query.But The following tips can help you maximize performance of store procedure 1:SET NOCOUNT ON 2.Use return values 3.Don't write select * from [tablename] write select [columnname1],[columnname2] from [tablename] This helps to speed of the query. 4.Don't use Prefix "Sp_" in your store procedure. 5.Use sp_executesql stored procedure instead of the EXECUTE statement. 6.Avoid using temporary tables inside your stored procedure.

The following three tips can help you maximize performance when you're using stored procedures.

1:SET NOCOUNT ON
This help to stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.

This can reduce network traffic.

2.Use return values

3.Don't write select * from [tablename]

write select [columnname1],[columnname2] from [tablename]
This helps to speed of the query.

4.Don't use Prefix "Sp_" in your store procedure.

Becoz if you use "Sp" then SQL Server looks in the master database then your database.

5.Use sp_executesql stored procedure instead of the EXECUTE statement.

6.Avoid using temporary tables inside your stored procedure.

Becoz Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

7.Avoid using DDL (Data Definition Language) statements inside your stored procedure.

Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

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

Responses to "Hoe to Increase SQL Server stored procedure performance"

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

Feedbacks

Post Comment:

Notify me by email when others post comments to this article.

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.

	Next Resource: What is page lifecycle for master and content page in asp.net
	Previous Resource: Widely asked in interviews: Delegate, Linq, Lambda Expn Create Extension Method & Expression Tree
	Return to Resources
	Post New Resource
	Category: Technical Interviews

Post resources and earn money!

More Resources

Popular Tags

Tag posting guidelines

Search Tags

Stored Procedures Optimization . Increase stored procedure performance .

Follow us on Twitter: https://twitter.com/dotnetspider

Active Members
Today

Last 7 Days

baskar (371)
Ultimaterengan (255)
Asheej T K (228)

more...

Awards & Gifts

Email subscription

About Us Contact Us Copyright Privacy Policy Terms Of Use Revenue Sharing sites Advertise Talk to Tony John
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
2005 - 2012 All Rights Reserved.
.NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
Articles, tutorials and all other content offered here is for educational purpose only.
We are not associated with Microsoft or its partners.