Using different tables based on date range

[goofaholix]

I have reports based on an operational data store / data warehouse type database.

I'm trying to find some solutions to help with our performance issues, many reports are highly parameterised and also need to return detailed data so scheduling, caching, and aggregate tables don't really suit.

95% of the time reports are only looking at the past couple of years data but we need to be able to report 10+ years worth of data at times.

I wondered if it would be possible to set something up where we had two tables for each table, one containing all data the other containing only the past couple of years data. Then based on the start date of the report it would either select from the smaller table or the bigger table if necessary.

I don't want to create this multiple times in each proc or report query as I don't think it's worth the extra hassle.

I thought I could do it as a function so I would use instead of the table name dbo.FunctionName(@StartDate) the trouble is I understand a function like this may not be very efficient, so negating the purpose. Also I think I'll have to do it as a multi value function which means itemising each column name and creating more maintenance each time I add or modify a column in my table(s).

Does anybody have suggestions on how I could achieve this without creating too much extra maintenance/development effort?

Thanks

Bruce

http://homepages.woosh.co.nz/bf/

 

[markros]

If suggest to create 3 stored procedures. One with the dates and the
IF ConditionToGetRecentData
execute spGetRecentData
ELSE
execute spGetArchiveData



PluralSight Learning Library

 

[RiverGuy]

Have you investigated your table indexing strategy? Try to keep it as one table if possible.

 

[goofaholix]

I could do it as a stored procedure, or 3, but then I couldn't join it to other tables as if it were a table which I think means I'd need to do one stored procedure per query, possibly one per report, so a lot of extra code to maintain.

I've tried indexing by my tests hacve shown it makes the queries slower or at least no better, not faster.

Thanks

Bruce

http://homepages.woosh.co.nz/bf/