February 4, 2009 at 1:10 pm
Hello,
We are using SQL Server 2000. We have a database which has been growing for for the last 3 years, and is currently at 64GB. Upon investigation, I found out that the database has many tables, and one of them - table "A" - is the largest, and is taking up ~ 40GB (20GB for data and 20GB for indexes). This table has 15 columns and we keep data in it forever (i.e. do not purge it).
After talking to developers, I found out that we only need 2 columns forever, and others only for 1 year. Thus came a question of how to use this knowledge to decrease the table (database) size. The goal was to come up with an approach which is easy to maintain, and would decrease the space usage as much as possible, and improve overall performance. We brainstormed 3 approaches and they were:
Approach 1
----------
1. Create "A_Archive" table with 2 columns only
2. Move the data from table "A" to "A_Archive" which is older than 1 year
3. Purge data from the "A" table, which is older than 1 year
4. Create a job, which would periodically (daily?) move the data from table "A" to table "A_Archive" and purge day's worth of old data from table "A"
Approach 2
----------
1. Create "A_Archive" table with 2 columns only
2. Move ALL the data from "A" to "A_Archive"
3. Purge data from the "A" table, which is older than 1 year
4. Modify the stored procedure which inserts into "A" table to simultaneously insert into the "A_Archive" table
Approach 3
----------
1. Change the columns in the "A" table to be NULLable (those which are not)
2. NULL the data which is > 1 year old, for all except the 2 columns we need to keep forever
Here are my thoughts:
Approach 1 decreases more space than Approach 2
Approach 2 is easier to maintain than Approach 1
Approach 3 I don't like, as it would require changing the columns to be NULLable and I am not even sure whether it will decrease the space
My questions are:
1. Which of these approaches would you advise?
2. Are there any other possibilities that any of you used to achieve the same goal?
3. When the data is NULLed (as in Approach 3), is the space really released and database size decreases? If so, does it depend on where in the table the column is - that is (last column or middle column) or not?
Thanks a lot in advance!
February 4, 2009 at 1:23 pm
I'd tend to go with the first one. Doesn't depend on future developers remembering/understanding that it has to insert into two tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply