MS SQL Server 2008 - Cleanup Stored Procedure
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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)
and insure DB maint task ran: reindex; update stats;checkdb
---
and review the code for CTE usage ( looks like it is a good candidate)
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...
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...
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
/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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial