Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sagitariusmzi
Posting Yak Master
113 Posts |
 Posted - 2009-11-16 : 06:54:39
|
| Hi ,I am creating a new web based application with data entry over 1 million record each month in a single table.By the time, the table will become hard to manage and application will perform slow.What solutions are available to manage large tables?I am using Sql Server 2005Thanks |
|
|
Pradip
Starting Member
32 Posts |
Posted - 2009-11-16 : 07:57:09
|
| Best solution is partitioning tables for each month, or plan accordingly. I could explain more if you wrote the details about frequency of hits on part of table.pradipjain |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 09:21:23
|
| I doubt I'd partition by month for only a million records per month. maybe by year. the key is going to be proper indexing. Review query plans and measure number of reads/writes. You should also test your application under simulated load. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-11-16 : 13:08:55
|
| Maybe partitioning by month is overkill, but that depends on how much these records will be read. If you're just doing inserts, there isn't much of a point but I can see some value if they're querying by month/year. It's very app specific so there's a lot of "it depends". At minimum I'd say partition by year so you can move your old data out more easily.Mike"oh, that monkey is going to pay" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-11-16 : 15:12:48
|
| So the solution then is to hire Tara!Mike"oh, that monkey is going to pay" |
|
|
|
Pradip
Starting Member
32 Posts |
Posted - 2009-11-17 : 02:08:57
|
| Thanks for commenting on partition concept. Partitioning will only help if reading only last months record and not all database. It all depend if Data reading requirement is monthly basis. Thanks,WWW.Silicus.Compradipjain |
|
|
|
sagitariusmzi
Posting Yak Master
113 Posts |
Posted - 2009-11-17 : 06:28:46
|
| There is frequent transactions and searching in Database |
|
|
|
|
|
|
How to Manage Large tables / Databases