?
Solved

MS SQL Server 2008 - Cleanup Stored Procedure

Posted on 2012-12-20
5
Medium Priority
?
185 Views
Last Modified: 2013-01-03
Hi,

I have attached a Stored Procedure (CleanupSP).  This Stored Procedure takes 3.22 Hours to run.  I would like to clean it up so it will run faster?.  I did not create this, but someone told me that it deals with a vertical Table??? and that is why it is like it is???

Please Help and thanks
CleanupSP.docx
0
Comment
Question by:Amour22015
  • 2
  • 2
5 Comments
 
LVL 82

Expert Comment

by:arnold
ID: 38712214
How frequently does this sp have to be run?
The sp constructs five tables from complex data aggregation.
Presumably you can not alter the database structure using triggers to maintain an upto date.
Using views would sacrifice performance for seemingly an infrequent run of the query.
Checking the organization of the primary table does it include historic data or does it employ archiving where at the conclusion of the year? Does your db use partitioning based on the year??
If the data does not change, and if you can spare the storage, the sp coud create tables that include the @taxyear in the name removing the drop section.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38713285
for start: make sure to add appropriate indexes (run exec sp_helpindex for the tables in the query)
and insure DB maint task ran: reindex; update stats;checkdb
---

and review the code for CTE usage ( looks like it is a good candidate)
0
 

Author Comment

by:Amour22015
ID: 38727173
Hi and thanks

First let me say that I am new to Stored Procedures.

So I am at a loss on the comments made?


SP runs M, T, W at night

It was mentioned:
Checking the organization of the primary table does it include historic data or does it employ archiving where at the conclusion of the year? Does your db use partitioning based on the year??
If the data does not change, and if you can spare the storage, the sp coud create tables that include the @taxyear in the name removing the drop section

The primary table (Main Table)
does not use historic data it is based on Tax Year which in this case is 2012

the other parts I am not sure how to answer?


Mentioned:
for start: make sure to add appropriate indexes (run exec sp_helpindex for the tables in the query)
and insure DB maint task ran: reindex; update stats;checkdb
---

and review the code for CTE usage ( looks like it is a good candidate)

Need step-by-step on how to do this???

Thanks for any help and Happy new year...
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 38731049
please read\check the below step-by-step tutorials:
/BTW: do you have DBA in your company who can check DB maint tasks?/

SQL SERVER – Stored Procedure Optimization Tips – Best Practices
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

--

Query and Stored Procedure Optimization
http://www.sqlusa.com/articles/query-optimization/



Common Table Expressions(CTE) in SQL SERVER 2008
http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER-2008
0
 

Author Closing Comment

by:Amour22015
ID: 38741483
thanks
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question