August 15, 2013 at 3:50 pm
We get data from clients every quarter and load them into our database. We need to retain 10 quarters worth of data online and then it is archived off to flat files.
The process mainly looks for the current quarter and two quarters prior. So my plan was to maintain three quarters worth of data in the main table and have seven quarters in a history table. So right now, I would have 2012 Q4, 2013 Q1 and 2013 Q2 in the main table and 2011 Q1 to 2012 Q3 in the history table. Create a view that joins both the tables for anyone who might be looking for older data.
But the issue comes when our client resubmits the data for a quarter older than three quarters. Then I would not only need to replace the data for that quarter but also need two prior quarters to that quarter for processing. This means that I would have to move that data into main table. Each quarter there would around 16 million rows. So moving the data between tables on the fly would be a nightmare.
Any ideas?
Your time and suggestions are greatly appreciated.
August 15, 2013 at 4:11 pm
As you are only thinking aloud, it is difficult to know where to butt in, as information you have given is likely to be incomplete.
But if you are on Enterprise Edition, this sounds like a fit for a partitioned table, with one partition per quarter. If you are a on Standard Edition, a partitioned view will have to do. Again, one table per quarter.
Moving data between physical tables is probably something you want to avoid.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 16, 2013 at 3:19 pm
You should also consider putting the quarter as the first key column of the clustered index. Then you won't need to partition in any way as long as you specify an equality condition for the quarter in all your queries. Your clients can read any/all quarters and SQL will only have to read the rows necessary.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply