Solved

MS SQL Server 2008 - Cleanup Stored Procedure

Posted on 2012-12-20
5
173 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 77

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 42

Expert Comment

by:EugeneZ
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 42

Accepted Solution

by:
EugeneZ earned 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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