table size decisions

[dakotafox]

Due to growth of our company, our main table will be reaching and exceeding the million record size for a 13 month date range of activity. Would we be better breaking this into weekly and monthly tables of current weekly and monthly activity that would be accessed for reporting and then appended into the master OR continue working with this size table and work at optimizing our indexing and select statements.

Your collective experience should be informative and will be appreciated by me.

Once again thank you in advance.

 

[ramani]

If there is many manipulation in monthly activity, like summing up all records etc., then keeping two tables for the monthly and yearly helps. However, if many of the activities are done on-line such as balancing a customer account or stock balances, then keeping in one data file will not reduce speed. The reason is, whenever a current balance of a stock item or customer a/c need to be extracted and added up with b/f figures, this will take a longer time from a consolidated file. It depends on these type of processing needed on (only) the monthly tansactions which should guide you ind eciding which is best.
Best of luck ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[ramani]

Also think on the posibility of data curruptions. Monthly files gets secured without leaving the older data files exposed to such risks. Anyway, there is no alternative to backups. ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK