Google Search

Google
 

Thursday, June 14, 2007

Stored Procedures Optimization Tips

Use stored procedures instead of heavy-duty queries.

This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.


*****

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.

This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

*****

Call stored procedure using its fully qualified name.

The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

*****

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.

The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

*****

Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.

The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

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

The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

*****
Use sp_executesql stored procedure instead of temporary stored procedures.

Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

*****
If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.

The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

*****
Try to avoid using temporary tables inside your stored procedure.

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

*****
Try to 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.

*****
Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.

The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

*****
Use SQL Server Profiler to determine which stored procedures has been recompiled too often.

To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

*****

25 comments:

Anonymous said...

I am not certain the place you're getting your info, but good topic. I must spend some time finding out much more or understanding more. Thank you for excellent info I was on the lookout for this info for my mission.
Feel free to surf my site ; click the following internet page

Anonymous said...

Actually no matter if someone doesn't know afterward its up to other people that they will help, so here it happens.

my web-site - grand-jewelry

Anonymous said...

Hello there, just became alert to your blog through Google, and found that it's truly informative. I am going to watch out for brussels. I'll appreciate
if you continue this in future. A lot of people will be benefited from your
writing. Cheers!

Also visit my homepage; gauba
Also see my web page :: manfrotto

Anonymous said...

This is a topic that is close to my heart... Many thanks! Exactly where are your contact details though?


Here is my web page ... life quotes

Anonymous said...

Keep on working, great job!

My blog post Odd News

Anonymous said...

It's really a nice and helpful piece of info. I'm glad that you
shared this helpful info with us. Please stay us informed like this.
Thank you for sharing.

Here is my blog ... buy cheap gw2 gold

Anonymous said...

It's in reality a nice and useful piece of information. I'm satisfied that you just shared this useful info
with us. Please keep us informed like this. Thanks for sharing.



Also visit my web-site ... What to look for when purchasing compression setting clothing?

Anonymous said...

You really make it seem so easy with your presentation but I find this matter to be
actually something that I think I would never understand.

It seems too complicated and very broad for me. I am looking forward for your next post, I'll try to get the hang of it!

My blog ... http://chestfatburner.com

Anonymous said...

Woah! I'm really enjoying the template/theme of this blog. It's simple,
yet effective. A lot of times it's tough to get that "perfect balance" between superb usability and appearance. I must say you've done a excellent job with
this. Also, the blog loads super fast for me on Firefox.
Superb Blog!

Here is my site chestfatburner.com

Anonymous said...

Good day! I know this is kinda off topic but I was
wondering which blog platform are you using for this website?
I'm getting sick and tired of Wordpress because I've had problems with hackers and I'm looking at options for another platform. I would be fantastic if you could point me in the direction of a good platform.

Feel free to visit my website :: chestfatburner.com

Anonymous said...

Hi there! This is kind of off topic but I need some guidance from an established blog.
Is it tough to set up your own blog? I'm not very techincal but I can figure things out pretty fast. I'm thinking about setting up
my own but I'm not sure where to start. Do you have any tips or suggestions? Thank you

Here is my blog: successful excercises for management of " moobs "

Anonymous said...

I seriously love your blog.. Very nice colors & theme. Did
you make this website yourself? Please reply back as I'm trying to create my very own site and want to know where you got this from or what the theme is called. Many thanks!

my web blog; Spazio Kovan

Anonymous said...

Hello, I do believe your web site could possibly be
having internet browser compatibility issues.
When I take a look at your web site in Safari, it looks fine but when opening
in Internet Explorer, it's got some overlapping issues. I just wanted to provide you with a quick heads up! Besides that, wonderful blog!

My web-site: Spazio Kovan

Anonymous said...

It's going to be end of mine day, however before end I am reading this great post to improve my knowledge.

My web blog safe free music downloads DownloadShield

Anonymous said...

I am genuinely happy to glance at this webpage posts which consists of plenty of helpful information, thanks for providing such statistics.


Also visit my blog best movie torrent download

Anonymous said...

Hi would you mind stating which blog platform you're using? I'm
going to start my own blog in the near future but I'm having a hard time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I'm looking for something completely unique.
P.S Apologies for getting off-topic but I had to ask!

Check out my website: http://www.onhiddencam.info

Anonymous said...

It's actually a great and helpful piece of info. I am happy that you just shared this helpful information with us. Please stay us informed like this. Thanks for sharing.

Also visit my page; hotgirlsexcam.com

Anonymous said...

Howdy! This post could not be written any better! Reading through this post reminds me of my previous room mate!
He always kept chatting about this. I will forward this article to him.
Fairly certain he will have a good read. Thank you for sharing!


My web site :: free porn movies

Anonymous said...

Good day I am so happy I found your blog page, I really
found you by mistake, while I was researching on Aol for something else, Regardless I am here now and would just like to say cheers for a
fantastic post and a all round exciting blog (I also love the theme/design), I don’t
have time to go through it all at the moment but I have saved it and also included your RSS feeds, so when I have time I will be back to read a lot more, Please
do keep up the excellent jo.

my web blog :: teen hardcore movies

Anonymous said...

Simply wish to say your article is as astonishing.
The clarity on your submit is just great and i
can assume you're an expert in this subject. Well together with your permission let me to grab your RSS feed to stay updated with approaching post. Thanks 1,000,000 and please continue the rewarding work.

Feel free to surf to my site ... cuteteenporn.net

Anonymous said...

Howdy! Someone in my Facebook group shared this website with us so I came to look it over.
I'm definitely loving the information. I'm bookmarking
and will be tweeting this to my followers! Exceptional blog and fantastic design and style.


Here is my blog ... cfnmfever.net

Anonymous said...

Hey there! I could have sworn I've been to this site before but after checking through some of the post I realized it's new to me.
Anyhow, I'm definitely delighted I found it and I'll be bookmarking and checking back frequently!



my webpage :: funny porn bloopers

Anonymous said...

My spouse and I stumbled over here different web address and thought I might as well check things out.
I like what I see so now i am following you. Look forward to checking out your web page repeatedly.


Here is my website :: http://dabagirls.com/melissa/ (hot-gay-boys.com)

Andy Gregory said...

you will be provided with eye drops and area around reading glasses your eyes will be washed properly
http://www.finestglasses.com/reading-glasses.html

Unknown said...

Imagine a beautiful room that draws gasps of appreciation when people enter the room. If you are thinking of your living room, adjust your perspective and take a mental walk to your shower http://www.plumpinguk.co.uk/showers.html. A shower has endless possibilities to make a statement and ensure an invigorating wash every day.