October 31, 2009 at 3:17 am
I have an accounts module. The db structure and table structure is such that the performance is fine. I have a problem though the table which i use to store the daily transaction is growing carzy. For two months it has stored upto 2,500,000 records in one particular table. I am worried what wil i Do after 2/3 years. I thot of arhiving the data on yearly basis but the client wil not agree to it. Can any one suggest me on this please
October 31, 2009 at 3:31 am
What does "25 lakh" mean? 25.000, 25.000.000 or something different?
If it's 25.000 within two month I wouldn't worry about it. It would take about 7 years until you hit the million row, which still isn't much at all.
You should inform the client, that if he doesn't consider archiving at all he will have to monitor disk space and react as required. He will also put the application into the risk of performance decrease some day. But maybe the app isn't that large at all so neither of the above will become an issue until the hardware is replaced with something much more powerful than available today (like in 10...20 years or so...)
Another question (probably the more important one) would be: what are you doing with the table in question? Is it used at all, and if so, how often and what for? Maybe
October 31, 2009 at 3:45 am
25 lakh means 2,500,000 records in two months is getting stored in one table pls help
October 31, 2009 at 3:57 am
To repeat my question from above:
Another question (probably the more important one) would be: what are you doing with the table in question? Is it used at all, and if so, how often and what for? Maybe
October 31, 2009 at 3:59 am
if your client doesn't agree on archiving the data you should consider adding a new set of disks to build a RAID1, 5 or 10 (depends on budget) and use partitioned tables. Add a new filegroup and "point" it to the new RAID (big to store "old" data), create a partition on the table pointing to the new filegroup, and a partition function on account's date (year probably) so the "old", not this year, move to the new filegroup and this year's accounts will be on the "old" filegroup.
With this you and still make "full" selects (all data will be on the same table) but will be partitioned witch means this year's accounts will be much "faster" since the other year's will be on another partition.
Pedro
October 31, 2009 at 4:01 am
A daily insert happens in the table it is used to store the transactions debit and credit entries. Inserts wil happen daily. Updates occasionally and select wil also be used often for report generation
October 31, 2009 at 4:03 am
If that's the case then partitioning the table is your best option.
If year's function on partitioning isn't enough (still many records) consider having monthly basis partitioning.
Pedro
October 31, 2009 at 4:11 am
Hi
If disk space is not a problem and you need the data for reporting, I'm always a fan of data-warehousing. Create a second database (with all fine tips of Pedro) which contains the historical data - probably in a more selective design. With this database you can keep your OLTP database slim and fast and you are able to run large and complex reports on your warehouse database.
Greets
Flo
October 31, 2009 at 4:12 am
Thanks.
October 31, 2009 at 10:43 am
Just out of curiosity, do you know the client's motivation for not wanting to archive the table?
Something I have seen done:
1. Very large table broken up in to periods, maybe years (Archiving)
2. Build view defined as tables for Period_Yn UNION ALL, Period_Yn+1 UNION ALL, Period_Yn+2...
The view would be queried for reporting over periods spanning a single table's period.
I'm not sure why this method was chosen, or even if it was a good method?
Anyboday know more about this design and the pros and cons of it? And if it would be suitable to the requirements the author has, should the client agree to archiving?
October 31, 2009 at 1:19 pm
the porpouse of partitioned tables is to avoid that... UNIONs...
you can have better performance by using partitioned tables.
Pedro
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply