dynamic DDL - recreate "_last_2" views monthly
-
I've got a few horizontally partioned tables. They're partitioned by month, so they look something like:
revenue_200601
revenue_200602
revenue_200603
revenue_200604
revenue_200605
revenue_200606
...
I've got a ton of views that only look at the current, last 2, last 12, previous 5, etc... based on the month.
Every month, these views need to be redefined. For example, today (5/31/06), the vw_revenue_last_2 is looking at revenue_200604 and revenue_200605 tables. Tomorrow (new month) it will need to be redefined to look at revenue_200605 and revenue_200606.
I'd rather spend all day today developing something to handle the montly redefine than 2+ hours tomorrow going into each one and changing the code.
Is anyone out there in a similar sitch? I've got a few ideas, but none that are watertight, so any ideas are welcome.
-
How large are your tables? Would it be possible to instead have a single table for the last 12 months? (assuming that's about how far back your views go)
If it is, then you could each month just shift out the oldest months data into 'archives', and be able to keep your views static, perhaps even to lessen the number of different views,since you then can filter for the different month ranges that each view now does. (last, last 2, last 12 etc)
/Kenneth
-
Thanks Kenneth, but that's not a viable solution. Each month has about 400,000 rows, and the data goes back to 2002-01-01, so having one main table would make things too difficult to deal with. There are 5 or 6 tables being horizontally partioned like this one, so I need a strategy that will apply to all.
-
Gregory,
Why don't you create a partioned view across your partitioned tables.
Once you've got a partitioned view, you just query the view. So if you query the view asking it to return data between 5/1/06 and 30/6/06 the SQL Server will know which tables it needs to access behind the scenes.
In this way you get away from ever having to specify the underlying table names - SQL Server just does that for you.
You'll need to change your "ton of views" so that they point to the newly created partitioned view. But at least you'll only need to make that code change once.
Check out "Create a Partioned View" and "Using Partitioned Views" in BOL. Just ignore the bits about distributed views as it sounds that you've got everything on the one local server.
Hope that helps,
-
If partitioned views don't meet your needs, then something like the following should work. You'd need to adapt a similar concept for the 12 month, etc. versions, but that should be fairly straightforward.
CREATE PROCEDURE dbo.uspGetLastTwoMonthsRevenue
AS
DECLARE
@strSQL varchar(8000)
,@month1 varchar(2)
,@month2 varchar(2)
,@year1 varchar(4)
,@year2 varchar(4)
,@monthprior datetime
-- Get the date from last month, so we have a year and month to work with
SET @monthprior = Dateadd(mm,-1,Getdate())
-- Create the year strings
SET @year1 = Cast(Datepart(yyyy,@monthprior) AS varchar(4))
SET @year2 = Cast(Datepart(yyyy,Getdate()) AS varchar(4))
-- Create the two month strings
SET @month1 = Cast(Datepart(mm,@monthprior) AS varchar(2))
SET @month2 = Cast(Datepart(mm,Getdate()) AS varchar(2))
-- Zero pad if needed
SET @month1 = Replicate('0',2-Len(@month1)) + @month1
SET @month2 = Replicate('0',2-Len(@month2)) + @month2
-- Build the string to get the table data
SET @strSQL = 'SELECT ColumnName1, ColumnName2 FROM Revenue' + @year1 + @month1 + ' UNION SELECT ColumnName1, ColumnName2 FROM Revenue' + @year2 + @month2
EXEC (@strSQL)
-
Well, I was more thinking (hoping
) that it was perhaps like the last 12 months that was the 'active' part so to speak. Then you could just keep the last years data in a single table. (12 x 400k rows isn't really that much per se) When data is aged more than 12 months, it would be migrated to a more 'permanent' archivetable, like revenues_2004 or so...I really don't see the number of rows alone as any reason for partitioning, but there may indeed be other factors, as hardware available, usage patterns etc that influences what works and what does not.
From my view, your main problem is a design problem, where you've found that the evermoving target of new objectnames is the hard mark to hit. The best solution would be something that didn't involve code change as soon as time passes by.
/Kenneth
-
thanks gang. I'm going to go with grambow's idea of partitioned views on the partitioned tables. this makes the whole "last_2" thing a moot point. I'll just include the partition column in the where clause of all the queries.
Thanks again. you guys rock.
-
Gregory, just for exersise, copy data from all your revenue tables into one (using "UNION" query), set up clustered index on RevenueRecordDate column and run select from this table
WHERE RevenueRecordDate >= DATEADD(mm, -2, @QueryDate) AND RevenueRecordDate < @QueryDate
@QueryDate is the day ending the 2 months you are querying for. E.G., if you need a report for January and February @QueryDate = '2006-03-01 00:00:00.000'
If you'll find this query slower than yours I gonna be very surprised.
_____________
Code for TallyGenerator -
I appreciate the idea Sergiy, but...
Setting up one table from the partitioned tables alone is too tall of an order. Spacially, we can't afford it, and it would probably take a full day just to move the data.
Further, choosing that date column as the clustered index is a luxury we can't afford either. This is only one of many queries hitting the revenue data.
Incidentally, the partitioned view is working GREAT. I got a massive improvement once I set that up. Coupled with some other minor changes, the process in question is now running in 1/6th the time it used to take.
I'm now going through the rest of the database to see where else I can put partitioned views in place and hopefully get similar improvements. I'm worth my weight in gold around here right now.
ROCK OUT.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply