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 |
|
bhavesh.darji
Starting Member
11 Posts |
 Posted - 2011-08-19 : 04:07:52
|
| Hello Everyone,I want some idea regarding the database design for a big system.We have a scenario in which we have large amount of data is coming to our server.We have so many entities which send data after every 30 seconds means everyday it sends near about 3 to 4 thousands data. And we have such entities near about 10 thousands now.To store that data we use different table for each entity. Now it is combursome to maintain such amount of tables in database.So we are going to design new database for that. In that we are thinking to make one single table which has field name entity. So above thinking is good for database design or we have to do something else. Because now this single table have lacs of records everyday basis. So it is good or not. We thinking to use indexing and partiioning in SQL Server 2008. So please give me the idea about that.Bhavesh |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-19 : 04:15:19
|
| its perfectly fine to tables storing such an amount of data. We have similar tables which grow in range of multiples of lacs of records on daily basis. We've applied partitioning on them and also added proper indexes. Hence performance of queries are not affected. We even have an archiving strategy which will archive the older after a particular period and would ensure we've only required data in the partitions at any time.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
bhavesh.darji
Starting Member
11 Posts |
Posted - 2011-08-19 : 04:32:47
|
| Hey thanks for the immediate reply.OK. So can you share your archiving strategy. And also i want to know about the partitioning. Means on which field we have to make partition.We are thinking to make partition on below fields,1. Date field - means everyday is new partition. So that we can maintain the backup on daily basis. But in that i have doubt that is it making performance effect on the query or not.2. Entity name field - means we have thousands of entity so for each entity data is not so much big. So for each entity partition we can also apply indexing.Or if any other thing i have to take care for partition and index, please suggest me.Or you can guide me so that what factors i have to consider to make decision for partition and index. Because now we have entities in thousands but it increases and target for our system is to go up to 1 to 2 lac entity.So i am also thinking that for that amount of entities data goes in crores. So is it fine with single table for that also?Bhavesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-19 : 04:38:02
|
| we archive any data older than years as we will analysing only 2 year max worth of data.we're partitioning based on date field so that every week falls to new partition. we've also applied index on columns that are frequently used in search queries.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
bhavesh.darji
Starting Member
11 Posts |
Posted - 2011-08-19 : 04:43:11
|
| Thanks for the quick reply.I got my answer.But last thing i want to know is that how much amount of rows you have after a year in your single table. And on that table have you generate any reports on daily basis. Thanks again.Bhavesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-19 : 04:48:13
|
| we will be having rows in magnitude of 1000 crores. we're generating weekly reports based on data of these tables.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
bhavesh.darji
Starting Member
11 Posts |
Posted - 2011-08-19 : 06:30:36
|
| What is hardware configuration of your server?Bhavesh |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-08-19 : 07:35:40
|
you have 10 billion rows visakh?wow.Corey I Has Returned!! |
|
|
|
|
|
|
Database Design