June 14, 2010 at 5:23 pm
So, we're trying to write a stored procedure that feeds financial data to a report (SSRS). The financial data is split up into different tables by fiscal year, and this report always focuses on the current fiscal year. In the end, we'd like the stored procedure to determine which table to go to just depending on the date that the report is run -- but when we use enough dynamic SQL in the statement to secure that, the performance takes a real hit (especially as data in the table increases; IE, the fiscal year carries on and we receive and spend money.) Currently we're remembering to return to the code to update the table name to the new fiscal year -- sometimes before and sometimes after our end users notice that something is wrong.
What other methods should we try?
June 15, 2010 at 1:27 am
This was removed by the editor as SPAM
June 15, 2010 at 7:23 am
Mary,
from what it sounds like, if the data is split out by fiscal year, then instead of different tables, you could have one table that is partitioned by fiscal year. That way your query wouldn't change and based on the parameters (I'm assuming that fiscal year is passed in or could be calculated by the stored proc), it would just use the current partition
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 15, 2010 at 10:39 am
Yes, if partitioning is available, I think that is your best solution.
If not, maybe schedule a job that could check to see if a table create/'switch' needed done and, if so, do it automatically, using dynamic SQL (or whatever other method works best for you).
Scott Pletcher, SQL Server MVP 2008-2010
June 15, 2010 at 10:52 am
Thanks, all!
No, partitioning is not available, but scheduling a job that would update. . . that's a fascinating possibility.
Thanks again!
June 15, 2010 at 11:52 am
The job could even run every day, since it would just exit quickly if nothing needed done.
Scott Pletcher, SQL Server MVP 2008-2010
June 16, 2010 at 1:14 pm
Since you are doing this in a stored procedure anyhow, you can load the data into a temp table first. The stored procedure makes the decision on which table is used for the source table. Something like:
CREATE TABLE #ReportSource
( Somecolumns )
IF @PassedDate < @CutOffDate
BEGIN
INSERT INTO #ReportSource
(SomeColumns)
SELECT SomeColumns
FROM HistoricalTable
END
ELSE
BEGIN
INSERT INTO #ReportSource
(SomeColumns)
SELECT SomeColumns
FROM LiveTable
END
SELECT * FROM #ReportSource
Todd Fifield
June 16, 2010 at 1:34 pm
Here's another suggestion that should simplify your task considerably.
Instead of coding the actual table name in the prod queries, use a view name. This should allow you to get rid of the dynamic SQL and use straight SQL, which is much easier to deal with.
DROP and reCREATE the view as needed to use a different fiscal table.
For example:
EXEC sp_rename 'tablename', 'newtablename'
CREATE VIEW tablename
AS
SELECT * --or col1, col2, col3, ....
FROM schemaname.newtablename
Later, when the physical table to read needs to change:
DROP VIEW tablename
CREATE VIEW tablename
AS
SELECT * --or col1, col2, col3, ....
FROM schemaname.newtablename2
This would even allow you to concatenate tables in a special situation to pull from multiple other tables at once:
CREATE VIEW tablename
AS
SELECT * --or col1, col2, col3, ....
FROM schemaname.newtablename2
UNION ALL
SELECT * --or col1, col2, col3, ....
FROM schemaname.newtablename3
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply