Speed up stored procedure

traci1234 used Ask the Experts™
I have a stored procedure that I need to return occupancy, sales, etc. by Community (80+) by week for an entire year.  Currently I have two loops (1 for community, 1 for weekly dates) but it runs in 1 hour plus and I need the performance to speed up.  I have attached a copy of the current procedure.

This will be a report within SQL Reporting Services

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Maybe the following will help optimizing your stored procedure .

... always want to join the smallest table first.

Utilize indexes
One of the simplest ways to become a hero in your organization is to add indexes. While this is very simple, it is often overlooked. This index tutorial will help.

Find common queries
If you have a large procedure, it may be likely you also have the same query being executed multiple times. If this is the case, then instead of performing the same query multiple times, dump the results into a temp table and reference the temp table in the rest of your queries. This is critical for speed. There have been many queries I have optimized in the past that run the same query a few times in the stored procedure.

Always go SET based
This means, never use while loops or cursors. This should actually be #1 on the list, however I’m hoping you already know this. There should be no reason to use loops or cursors. It has been proven time and time again that everything can be solved using SET based methods. SET based just means a simple select statement.

Do Not Use Scaler UDF’s
Scaler user defined functions make a query run as if they were in a loop. This defeats the entire purpose. SQL is not object oriented, do not try to make it so. It is the habit of a front-end developer to modularize and object orient procedures. Do not try this with SQL. Many will pay dearly. Granted, SQL may seem as if it is behind the times, however you cannot possibly make anything execute faster. No front-end process will compare to any result set processed by SQL. That said, you can use a scaler UDF when setting a single variable, just do not use it in a SELECT statement that returns a result set.

Use Exists instead of JOIN
If you are doing an Inner Join to a table just to eliminate a result set, and the table you are joining is not contributing any columns to the select list, then you are creating a Worktable in the background and using extra IO resources. Use exists instead.

Avoid Distinct
The DISTINCT clause is basically a big group by. Well, it is slightly more optimal, however you get the point. Do not use this unless you can’t avoid it. If you do find yourself using it, then it is very likely you have a bad table design somewhere.

Limit the Select list
Returning too many columns can have a drastic effect on your query. Not only will it increase you chances for bookmark lookups (or key lookups), but the network and disk latency add to the query. Not to mention you will be squeezing more data into your buffer cache.

Use the Least Amount of Tables to Compile Your Select List
An example would be let’s say you need to join on 2 tables in order to get your result set. If one of the tables contains all the fields needed for the select list, but you are also able to get the same field(s) from the other table, always go with only returning the values from the one table. Doing so will limit the number of IO operations necessary to give you your result.

Index temp tables
Temp tables are treated just like permanent tables according to SQL. They can have indexes & statistics. The only downfall is that they often cause recompiles for the statement when the result sets differ. To counter this read reducing temp table recompiles or use table variables if you have to.

Learn execution plans
Bad times in execution plan-ville include:

•table & clustered index scans
•large number of output rows – dictated by thick arrows
•Key Lookups (bookmark lookups)
•Table spool – eager spool (halloween spools)
Avoid poor performing techniques
CTE’s – Common table expressions
Instead of using CTE’s use temp tables. (Yeah I said it). They perform badly. They do have a good use, and that is recursion.

Table Variables
Yeah I said this too. I know some developers will give me flack for this, however I have rarely seen a table variable perform well. Yes, if you have under 1000 rows, consider it. Otherwise, I suggest #temp tables.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial