Unanswered: updating daily information in a history table (was "Help-Brainstorming")
I have a big table which contains approx. 31,524,044 rows. The structure of the table look like this:
date ID A B C D
1/1/65 X Null Null Null Null
1/4/65 X 1 2 3 4
2/25/05 X 2 3 4 5
1/1/65 Y Null Null Null Null
1/4/65 Y Null Null Null Null
2/25/05 Y 2 3 4 5
The number of distinct(ID) is approx 3200 and each one has daily historical A, B, C, and D back for 40 years. For going forward I need to update daily information for 3200 ids. Currently, I am runing query against to this table is ok. I am thinking by the time pass by the table will be hudge since "stored historical information". It probably takes "long long long" time to run the query against this table. Any suggestion or comments... what is the best/better solution? Or it is not problem at all?
Is the query you are talking about which will take a "long long long" time one which appends data, updates data, or selects data? If you're just appending it probably does not make a big difference depending on whether indexes must be updated. If you are updating or selecting records it could affect the speed of the query depending on how the table is (or isn't) indexed.
Why not split the historical table up into several historical tables according to date? It's probably not necessary to have all 40 years of data on one table. That way when you're only working with the most recent data in a physical table. If you have a need to select across the entire history you can UNION all of the historical tables together in a view.
I do have index created on this table, DateID which is combined two fields- Date and ID. I need to update this table daily which means it will grow 3000 rows, 27,000(3000*9 fields) records per day. Should not I worry about it since I have index created on this table?
Now I just run into another problem which I posted ten minutes ago. Please review.