Click here to Skip to main content
15,867,568 members
Articles / Database Development

Top 5 Stored Procedure Performance Tips You Can Use

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
7 Mar 2022CPOL6 min read 16.9K   3  
Top five performance tips for Stored Procedures
In this post, we will dig into some super easy tips that can be used to speed up your stored procedures.

Knowing how to optimize stored procedures is important, so stick around and I’ll show top five stored procedure performance tips you can use today.

Let’s dig into some super easy tips you can use to speed up your stored procedures. Before we begin, let me point out that the tips I’m going to show you are specific to stored procedures.

This article is based on my video Five Tips You can Use To Speed Up Your Stored Procedures?

In addition to these tips, you’ll also want to look at how you can optimize the queries inside the stored procedures, but that’s an entirely different subject, so let’s get started.

Performance Tip 1: SET NOCOUNT ON

The first thing I want to talk about, the first easy stored procedure performance tip you can implement is to include the command SET NOCOUNT ON, in your stored procedure. What this does is it stops sending those messages that say five rows affected by this command, six row effected by this command…

If you have a stored procedure that’s doing a lot of queries or updates and you see those messages quite a bit when you run the command, this will reduce a lot of that network traffic.

If you think about an application that’s calling a stored procedure many, many times, this can really add up. So use SET NOCOUNT ON.

Also, what you want to do is call your stored procedures with their fully qualified names. So what do I mean by that?

Stored Procedure Performance Tip 2 – Use Fully Qualified Name

Your stored procedures are sorted in schemas. If you don’t do anything at all, it is stored in the dbo schema. By not fully qualifying the name, it takes longer to search for and then execute the stored procedure.

To execute the stored procedure named EmployeeLoad according to best practice, use this command:

SQL
EXEC dbo.EmployeeLoad

Do not use EXEC EmployeeLoad. Why? Because by not including the schema, you are making it a little harder for SQL to search through all the database objects to find your stored procedure to run.

Performance Tip 3 – Avoid sp_ prefix

Also, while we’re on the topic of naming stored procedures, do not begin your stored procedure with sp_.

I think people think, “Oh, that’s really cool. I can just use sp_ because that stands for stored procedure.”

Well, actually, it’s a Microsoft reserve word, and if you use that prefix, what will happen is that every time you go to run your stored procedure, the database starts to look through the master database and some other databases before it even gets to your database to look for your stored procedure. You’re just adding more to how it searches to find if stored procedure even exists.

Better yet, why not just name your stored procedure with no prefix. So the old school way of doing it used to be like, “Oh, I got to save my stored procedure as uspEmployeeLoad so everyone know it’s a stored procedure.

Well, we know it’s a stored procedure, you can look in the dictionary or your tool shows it under stored procedures. There’s no need to prefix it. Just call it EmployeeLoad. That is to cool school way of doing it.

Performance Top 4 – Avoid Cursors

The next stored procedure performance tip is to use set based queries over cursors whenever possible. What I mean is, instead of using cursors that iterate over rows and do while I’m not at the end of my record set, go to the next row and do something, preserve this value, and so on…

Use a window function because window functions rock, and you can use window functions to get the previous record, a row in your result set or the next record in your result set. You can do running averages with them, all sorts of things with window functions.

And you’ll find out that many of the reasons why you were using cursors in the first place can now be replaced by using Window functions or maybe a recursive CTE, like a recursive common table expression.

The idea though is to try to move away from cursors because they go step by step through the record set until it gets to the end and it’s very slow. So do not use those.

Performance Tip 5 – Decrease Transaction Scope

Also, one thing that you want to look at is reducing transaction scope. Obviously, when we’re working with our stored procedures, we’re going to need to, at some time update more than one table and that’s where transactions come in handy because I can make sure that during this operation, I only conclude the operation if both tables successfully get updated. So good use for a transaction, but do not put a whole bunch of other garbage in between those statements or add it into that transaction.

Like, “Oh, let’s go look up other things or run along queries,” because when a transaction is running, it’s locking. And the longer the locks are held, the greater the chance that you get deadlocks.

A deadlock is basically like, I’m trying to lock the record that you have locked and you’re trying to lock a record that I have locked. And so, we get into this deadly embrace where we essentially both stop because we’re stuck. I’m waiting for you and you’re waiting for me and we both can’t stop because we each are using resources that each of us need. So dead locking’s bad.

You can reduce dead locking if you can reduce the time that you are actually locking things in your system and a good way to reduce the time of locking is just to make sure that when you’re doing a transaction that you get in, do the transaction and get out. Don’t put a lot of fluff in there.

Conclusion

So those are my tips. They’re real quick. They’re really easy. I hope that you can start to implement them. Some of them are quick commands. Some are more philosophical. There’s no examples of this one. It’s just more for you to think about and I will probably do examples down the road, but this is more just for you to start getting your gears going on what you’d want to do next. So if there’s anything you want me to talk about, first of all, please subscribe to my channel. Yay, do that. That makes me happy. And then write a comment about what you think would be a great topic for me to cover because I pay attention to that and I will add it in.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --