October 12, 2012 at 9:05 am
Hello,
Here is my situation; I have a stored procedure that will be executed 'ad-hoc' as a report. The stored procedure has several temp tables being executed and does some aggregations, such as SUM, percent’s, etc. The procedure can take close to 5 minutes to execute, which is a long time.
What I'm thinking about doing is using a 'holding table' with a second stored procedure to capture the result set from the first or main stored procedure. What I'm thinking about doing is executing the stored procedure several times throughout the day, or, somehow add a trigger to the dependent tables that would update the holding tables with values so that the second stored procedure can run in an acceptable amount of time.
Any thoughts or suggestions would be much appreciated.
Thank you for your time.
October 12, 2012 at 9:36 am
PFlorenzano-641896 (10/12/2012)
Hello,Here is my situation; I have a stored procedure that will be executed 'ad-hoc' as a report. The stored procedure has several temp tables being executed and does some aggregations, such as SUM, percent’s, etc. The procedure can take close to 5 minutes to execute, which is a long time.
What I'm thinking about doing is using a 'holding table' with a second stored procedure to capture the result set from the first or main stored procedure. What I'm thinking about doing is executing the stored procedure several times throughout the day, or, somehow add a trigger to the dependent tables that would update the holding tables with values so that the second stored procedure can run in an acceptable amount of time.
Any thoughts or suggestions would be much appreciated.
Thank you for your time.
I would recommend looking into making your proc faster. That is the real issue here. Creating additional tables and/or triggers is fixing the symptom, not the problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2012 at 11:32 am
Good point Sean,
We have another solution in place that we may implement.
October 12, 2012 at 2:23 pm
“Relational Epiphany”
That is a great phrase Joe!!! Relevant to both SQL and marriage. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply